I’ve seen a presentation on the 11G new features from Alex and Lucas at Amis. One of the questions posed there was when you are using the new follows option in triggers, what would happen if you have two triggers following the same one. A bit like the way it”s done right now.
Suppose you have multiple trigger on the same event on the same table, for instance when you have a packaged application where you are not allowed to change any of the code. But you want to add your own code. Or you have some standard triggers that you generate using for example CodeGen. If you add a trigger to an event that already has a trigger on it, there is no way of knowing when this is executed, before or after the existing trigger. If you rely on values that may be changed in the first trigger there was no way to be sure this trigger had been fired before your code is executed.
In Oracle 11G you now have the possibility to add the follows option to a trigger, to make sure the code is executed after the original trigger. (from the documentation at Oracle)
CREATE [ OR REPLACE ] TRIGGER [schema.] trigger { simple_dml_trigger | compound_dml_trigger | non_dml_trigger } [ FOLLOWS [schema.] trigger [[schema.] trigger]...] [ ENABLE | DISABLE ] [ WHEN (condition) ] trigger_body
When you add the FOLLOWS clause to your trigger, you can make sure it gets fired after the other trigger. But what happens if you have two (or more) triggers that follow a single trigger? One table with three triggers in place. Trigger 2 follows trigger 1 and trigger 3 follows trigger 1 too. Which one gets fired first? Is there any way of knowing? Someone once said that the on with the lowest physical number will be fired first? But how is this calculated?
I have created a script to try these issues:
SET FEEDBACK OFF prompt Create a table CREATE table T_TRIGGERTEST ( ID NUMBER not null, VALUE VARCHAR2(200) ) tablespace USERS; -- Create/Recreate primary, unique and foreign key constraints alter table T_TRIGGERTEST add constraint PK_ID primary key (ID); prompt create a sequence... CREATE SEQUENCE t_triggertest_seq START WITH 1 NOCACHE; prompt create trigger 1... create or replace trigger tr_triggertest_1 before insert on t_triggertest for each row BEGIN :new.id := t_triggertest_seq.nextval; :new.value := :new.value || ' tr_triggertest_1'; end tr_triggertest_1; / prompt create trigger 2... create or replace trigger tr_triggertest_2 before insert on t_triggertest for each ROW follows tr_triggertest_1 begin :new.value := :new.value || ' tr_triggertest_2'; end tr_triggertest_2; / prompt create trigger 3... create or replace trigger tr_triggertest_3 before insert on t_triggertest for each ROW follows tr_triggertest_1 begin :new.value := :new.value || ' tr_triggertest_3'; end tr_triggertest_3; / prompt insert a record into the table... INSERT INTO t_triggertest (value) VALUES ('value'); prompt query the table to see the values SELECT * FROM t_triggertest; prompt create trigger 2 again... create or replace trigger tr_triggertest_2 before insert on t_triggertest for each ROW follows tr_triggertest_1 begin :new.value := :new.value || ' tr_triggertest_2'; end tr_triggertest_2; / prompt insert another record... INSERT INTO t_triggertest (value) VALUES ('value'); prompt query the table to see the values... SELECT * FROM t_triggertest; prompt drop trigger 3... DROP TRIGGER tr_triggertest_3; prompt insert another record... INSERT INTO t_triggertest (value) VALUES ('value'); prompt query the table to see the values... SELECT * FROM t_triggertest; prompt create trigger 3 again... create or replace trigger tr_triggertest_3 before insert on t_triggertest for each ROW follows tr_triggertest_1 begin :new.value := :new.value || ' tr_triggertest_3'; end tr_triggertest_3; / prompt insert another record... INSERT INTO t_triggertest (value) VALUES ('value'); prompt query the table to see the values SELECT * FROM t_triggertest; prompt create trigger 3 again, but this time it follows trigger 2 create or replace trigger tr_triggertest_3 before insert on t_triggertest for each ROW follows tr_triggertest_2 begin :new.value := :new.value || ' tr_triggertest_3'; end tr_triggertest_3; / prompt insert another record... INSERT INTO t_triggertest (value) VALUES ('value'); prompt query the table to see the values... SELECT * FROM t_triggertest; prompt clean up, drop the table and the sequence... DROP TABLE t_triggertest; DROP SEQUENCE t_triggertest_seq; output (partially) ID VALUE -- -------------------------------------------------------------------------------- 1 value tr_triggertest_1 tr_triggertest_3 tr_triggertest_2 2 value tr_triggertest_1 tr_triggertest_3 tr_triggertest_2 3 value tr_triggertest_1 tr_triggertest_2 4 value tr_triggertest_1 tr_triggertest_3 tr_triggertest_2 5 value tr_triggertest_1 tr_triggertest_2 tr_triggertest_3
When I check the output it appears that trigger 3 gets fired before trigger 2 no matter when it’s created except when I tell it to fire following trigger 2. I am guessing you can get some other results on your installation of Oracle 11G, but I think this shows that you cannot rely on the order of triggers being fired, except when you tell the triggers to follow eachother,