Triggers

Trigger
Triggers are simply stored procedures
that are ran automatically by the
database whenever some event (usually
a table update) happens.
Event-Condition-Action model
Trigger mechanism:
Specify when a trigger is to be
executed.
It is broken into an event that
causes the trigger to be checked and
as condition that must be satisfied
for trigger execution to proceed.
Trigger ... Contd.
Create [or Replace] trigger trigger_name
{Before| After | Instead of }
{Insert [or] | Update [or] | Delete}
[of col_name]
on table_name
[Referencing old as o New as n]
[for each row]
When(condition)
Declare
Declaration-statements
Begin
Executable-statements
Exception
Exception-handling-statements
End;
Trigger ... Contd.
 Create [or Replace] trigger trigger_name : Creates or
replaces an existing trigger with the trigger_name.

 {Before| After | Instead of } : This specifies when the

trigger would be executed. The Instead of clause is
used for creating trigger on a view.

 {Insert [or] | Update [or] | Delete} : This specifies the

DML operation.

 [Of col_name]: This specifies the column name that

would be updated.

 [On table_name]: This specifies the name of the table

associated with the trigger.
Trigger ... Contd.
 [Referencing old as o New as n]: This allows you to
refer new and old values for various DML statements,
like Insert, Update, and Delete.

 [for each row]: This specifies a row level trigger, i.e.,

the trigger would be executed for each row being
affected. Otherwise the trigger will execute just once
when the SQL statement is executed, which is called
a table level trigger.

 When(condition): This provides a condition for rows

for which the trigger would fire. This clause is valid
only for row level triggers.
 PL/SQL Trigger Execution
Hierarchy

The following hierarchy is followed when a

trigger is fired.
1. BEFORE statement trigger fires first.
2. Next BEFORE row level trigger fires, once
for each row affected.
3. Then AFTER row level trigger fires once
for each affected row. This events will
alternates between BEFORE and AFTER row
level triggers.
4. Finally the AFTER statement level trigger
fires.
Permissions
grant create trigger to alex;

create table person ( id int, name

varchar(30), dob date, primary key(id) );
Trigger Example 1
SQL> select * from student;
NAME NO
---------- ----------
a 1
b 2
c 3
d 4
Trigger Example 1...
Contd.
SQL> create or replace trigger dd
2 before insert on student
3 for each row
4 begin
5 delete from student where no=1;
6 end;
7 /

Trigger created.
Trigger Example 1...
Contd.
SQL> insert into student values('e',5);
1 row created.
SQL> select * from student;
NAME NO
---------- ----------
b 2
c 3
d 4
e 5
If you need to set a column value in an inserted
row via trigger, then you need to use a before
insert trigger in order to access the new values.
Trigger Example 2
SQL> create or replace trigger ddd
2 before update on student
3 for each row
4 when (old.no/new.no>0)
5 begin
6 insert into student1
values(:old.name,:old.no);
7 end;
8 /
Trigger created.
Trigger Example 2...
Contd.
SQL> select * from student;
NAME NO
---------- ----------
q 2
c 3
d 4
e 5
a 1

SQL> select * from student1;

no rows selected
Trigger Example 2...
Contd.
SQL> update student set name='ee' where no=1;
1 row updated.
SQL> select * from student1;
NAME NO
---------- ----------
a 1
SQL> select * from student;
NAME NO
---------- ----------
q 2
c 3
d 4
e 5
ee 1
 Before Insert Trigger
create or replace trigger person_insert_before
before insert on person
for each row
begin
dbms_output.put_line(’before insert of ’
||:new.name); end;

insert into person(id,name,dob) values

(1,’john’,sysdate);
before insert of john 1 row created.
After Insert Trigger
 create or replace trigger person_insert_after
after insert on person
for each row
begin
dbms_output.put_line(’after insert of ’
|| :new.name); end;

 insert into person(id,name,dob) values

(2,’jane’,sysdate);
before insert of john
after insert of jane

Both triggers have fired. One before the insert, the other
one after
 Before Update Statement
Trigger
create or replace trigger
person_update_s_before
before update on person
begin
dbms_output.put_line(’before updating
some person(s)’); end;

update person set dob = sysdate;

2 rows updated.
Trigger only fired once. this is because we did
not specify for each row
FOR EACH ROW Before
Update Trigger
 create or replace trigger person_update_before
before update on person
for each row
begin
dbms_output.put_line(’before updating ’ ||
to_char(:old.dob,’hh:mi:ss’) || ’ to ’ ||
to_char(:new.dob,’hh:mi:ss’));
end;

 update person set dob = sysdate;

before updating some person(s)
before updating 10:54:06 to 11:10:01
before updating 10:54:06 to 11:10:01 2 rows updated.
 Special IF statements
 Use if statements to determine what statement caused the
firing of the trigger.

create or replace trigger person_biud

before insert or update or delete on person
for each row
begin
if inserting then
dbms_output.put_line(’inserting person: ’
|| :new.name);
elsif updating then
dbms_output.put_line(’updating person: ’ || :old.name
|| ’ to ’ || :new.name);
elsif deleting then
dbms_output.put_line(’deleting person: ’
|| :old.name);
end if;
Special IF statements ...
Contd.
insert into person(id,name,dob) values
(3,’superman’,to_date(’09/05/1950’,’mm/dd
/yyyy’));
inserting person: superman

update person set name = ’batman’ where

name = ’superman’;
updating person: superman to
batman

delete person where name = ’batman’;

deleting person: batman
 Working with Views
create or replace view person_view as select name
from person;
create or replace trigger person_view_insert
instead of insert on person_view
for each row
begin
dbms_output.put_line(’inserting: ’ || :new.name);
end;
insert into person_view(name) values (’superman’);
inserting: superman

Fire a trigger when someone tried to insert a value into

a VIEW values are not inserted into views
Trigger Exceptions
An error (exception) in a trigger stops the
code from updating the database.
create or replace trigger person_dob
before update of dob on person
for each row
begin
raise_application_error(-20000,’cannot
change date of birth’);
end;
Trigger Exceptions ... Contd.
This effectively halts our trigger execution, and
raises an error, preventing our dob from being
modified. An error (exception) in a trigger stops
the code from updating the dob.
update person set dob = sysdate;
update person set dob = sysdate
*
error at line 1:
ora-20000: cannot change date of birth
ora-06512: at "particle.person_dob", line 2
ora-04088: error during execution of trigger
particle.person_dob’
Viewing Triggers
To view all user defined triggers by doing a
select statement on user_triggers.
For example: select trigger_name from
user_triggers;
Dropping Triggers
drop trigger trigger_name;
Altering Triggers
alter trigger trigger_name [enable|disable];

alter trigger person_dob disable;

update person set dob = sysdate where
name = ’john doe’;
alter trigger person_dob enable;