Email Us : sunilkumark11@gmail.com
Free Training > SQL PL / SQL Forms Reports Oracle Applications
< Previous Next >

 
PL / SQL Triggers

Trigger is a PL/SQL block which is executed automatically
basing on a event.

Triggering events: Insert, Update, Delete

Trigger timings: Before, after, instead of

Syntax:
------

Create or replace trigger <trg_name>
<timing> <event> on <table_name>
begin
.............
.............
.............
end;
/

ex:
----

create or replace trigger trg1
after insert on dept
begin
dbms_output.put_line('Thank You');
end;
/

Trigger Created.

Now, when we peroform the event, trigger is executed,.

ex:
----

insert into dept values (52,'HR','HYDERABAD');

Thank You

1 row created.

We get the message, 'Thank You'.
That means trigger is executed.

We can create triggers on multiple events.

ex:
-----

create or replace trigger trg1
after insert or update or delete on dept
begin
dbms_output.put_line('Thank You');
end;
/

Trigger created.

Now, for all the three events , triggger is fired.

ex:
--

Update dept set loc='DELHI'
where deptno =10;

Thank You
1 Row updated.

delete from dept where deptno=50;
Thank you
1 Row deleted.

In the above program, we get the same message for all the events.

We can also have different messages to be displayed, basing on the events.

Ex:
------

create or replace trigger trg1
after insert or update or delete on dept
begin

if inserting then
dbms_output.put_line('Thank You for inserting');
elsif updating then
dbms_output.put_line('Thank You for updating');
else
dbms_output.put_line('Thank You for deleting');
end if;

end;
/

Trigger created.

In the above program, inserting and updating are the key words which are used to identify the events.

Triggers can be classified into two types, basing on the no of times it is executed.

1) Statement level triggers
2) Row level triggers

1) Statement level triggers are executed only once, irrespective of no of rows effected by the event.

2) Row level triggers are executed for every row effected by the event.

To create a row level trigger, we need to use
for-each-row clause.

ex:
-----

create or replace trigger trg1
after update on emp for each row
begin
dbms_output.put_line('Thank you for updating');
end;
/

Trigger created.

update emp set sal=2000
where deptno=10;

Thank you for updating
Thank you for updating
Thank you for updating

3 rows updated.

As, the update command is effecting 3 rows, trigger is executed 3 times.
These kind of triggers are called row level triggers.

Triggers are used to enforce business rules by
using :OLD and :NEW qualifiers.

ex:
----

Create a trigger which restrict insert operation
if sal >5000.

create or replace trigger trg1
before insert on emp for each row
begin

if :new.sal >5000 then
raise_application_error(-20150,
' Sal cannot be more than 5000');

end if;

end;
/

Trigger Created.

Event:
--------

insert into emp( empno, ename,sal, deptno )
values (1111,'ARUN', 6000,10);

ERROR:
ORA-20150, sal cannot be more than 5000

 

Ex:
--------
Create a trigger which restrict delete operation on emp
if job is president.

create or replace trigger trg1
before delete on emp for each row
begin

if :OLD.JOB='PRESIDENT' then

raise_application_error(-20151,
' cannot delete president');

end if;

end;
/

Trigger created.

Event:
---------

delete from emp where ename='KING';

Error:
ORA-20151, cannot delete president

Instead of triggers:
------------------------

Instead of triggers are helpful to perform DML operations on complex view.

Example of complex view:
------------------------

create or replace view emp_dept_v
as
select e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno;

View created.

Generally, we cannot insert row into complex view.
But, by using the instead of triggers, we can do it.

ex:
----

create or replace trigger trg1
instead of insert on emp_dept_v for each row
begin

insert into dept values (:NEW.deptno,:NEW.dname, :NEW.loc);

insert into emp ( empno, ename,sal, deptno) values
(:NEW.empno, :NEW.ename,:NEW.sal, :NEW.deptno);

end;
/

Trigger Created.

Event:
------
insert into emp_dept_v values (2121,'VIJAY',3000,60,'TRAINING','HYDERABAD');

1 Row created.

To see the list of triggers:
-------------------------------
select trigger_name from user_triggers;

To drop a trigger:
------------------
Drop trigger <trigger_name>;

Ex:
-----
Drop trigger trg1;

Trigger Droped.

 


< Previous Next >