Predictable execution order of triggers

This entry is part 7 of 8 in the series New Pl/Sql features 11g

Prior to 11g if you defined multiple triggers of the same type on the same table it was impossible to guarantee which trigger would fire first. Oracle 11g release includes a new instruction FOLLOWS. This allows you to specify which trigger “FOLLOWS” the other triggers.
The basic syntax is:

FOLLOWS <trigger_name>, <trigger_name2>, ...

You can see from the syntax it’s possible to make a trigger depend on multiple other triggers.
You can review the triggers dependencies in the USER_DEPENDENCIES table.

An example

When a new employee is hired a vacation bonus of 10% is added to the base salary. Furthermore an additional 100 is added for expenses. These business rules are added as 2 triggers on the EMP table. However prior to 11g there’s no control over which fire first and the firing order does make a difference in salary
Depending on order this is the computation:

  • 3500*1.1+100 = 3950
  • (3500+100)*1.1=3960

As the expenses fee is an extra benefit. It should not be used as a base for the vacation bonus. The outcome should be 3950, not 3960. Using the FOLLOWS instruction you can make sure the triggers always fire in the first order.

Now if a new  employee is added to the table the result is predictable
insert into emp values ('1000', 'APODICTUS', 'PL/SQL', null,  sysdate, '3500', null,'10');
1 rows inserted

select sal from emp where empno=1000;

Series Navigation<< PL/SQL Function Result CacheCompound triggers >>

Posted in Oracle, Triggers Tagged with: , ,

Leave a Reply