Wednesday, 13 June 2018

Trigger Execution Order

We are all aware that we can write multiple triggers on a single table based on the business requirement.

Execution order 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.
Prior to 11g, there was no sure answer for this type of situation. Any one trigger (if they are of the same type) can get executed first and there is no guarantee that triggers will be executed again in the same order. This can create issues with the  data. In 11g, Oracle introduced 'FOLLOWS' clause to control the execution order of the triggers when they are of the same type.

No comments:

Post a Comment