Posted by In early Oracle releases, you could have 1 trigger per type of trigger per table (eg if you had a before-row-insert trigger, you could have only 1). Now there is no limit. But that does raise the question – if I have “n” BEFORE ROW triggers on a table, in which order do they fire ? Let’s look at the following example:
SQL> drop table t1 purge;
Table dropped.
SQL> create table T1 ( x int, y int, z int );
Table created.
SQL> create or replace
2 trigger trg1
3 before insert on t1
4 for each row
5 begin
6 :new.y := :new.x;
7 end;
8 /
Trigger created.
SQL> create or replace
2 trigger trg2
3 before insert on t1
4 for each row
5 begin
6 :new.z := :new.y;
7 end;
8 /
Trigger created.
Seems simple enough…Copy ‘x’ into ‘y’, and then copy ‘y’ into ‘z’. So lets see what happens
SQL> insert into t1 (x) values (1);
1 row created.
SQL> select * from t1;
X Y Z
---------- ---------- ----------
1 1
Where did “Z” go ? What happened was TRG2 fired first, and then TRG1 fired. The firing order is indeterminate.
To solve this, we can use the FOLLOWS command to dictate the order in which triggers must fire.
SQL> create or replace
2 trigger trg2
3 before insert on t1
4 for each row
5 follows trg1
6 begin
7 :new.z := :new.y;
8 end;
9 /
Trigger created.
SQL> insert into t1 (x) values (1);
1 row created.
SQL> select * from t1;
X Y Z
---------- ---------- ----------
1 1
1 1 1
SQL>
SQL>
So now you can see (if you have a lot of triggers) where FOLLOWS might come in useful.
Learning is not a spectator sport 