The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]

Menu

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)

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?

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,