TechView with Kamlesh khaliya

Resources for OCP, Oracle Interviews(kamleshkhaliya@gmail.com)

Friday 22 April 2011

What is the execution order of triggers in oracle?

Oracle Database executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on a single table, then Oracle Database chooses an arbitrary order to execute these triggers(but 11g introduced FOLLOWS clause to define the order). Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values. The old values are the original values,
and the new values are the current values, as set by the most recently fired UPDATE or INSERT trigger.

Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:
1.Run all BEFORE statement triggers that apply to the statement.
2.Loop for each row affected by the SQL statement.
a. Run all BEFORE row triggers that apply to the statement.
b. Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c. Run all AFTER row triggers that apply to the statement.
3.Complete deferred integrity constraint checking.
4.Run all AFTER statement triggers that apply to the statement.

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home