Tuesday, May 4, 2010

Firing Order of same type trigger (In oracle 10g)

Firing Order of same type trigger (In oracle 10g)
Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

Although triggers of different types are fired in a specific order, triggers of the same type for the same statement are not guaranteed to fire in any specific order. For example, all BEFORE row triggers for a single UPDATE statement may not always fire in the same order. Design your applications so they do not rely on the firing order of multiple triggers of the same type.
The only thing, can say about the order of triggers firing :
• All of the BEFORE triggers will fire in some order.
• Then all of the BEFORE FOR EACH ROW triggers will fire in some order.
• Then all of the AFTER FOR EACH ROW triggers will fire in some order.
• Then all of the AFTER triggers will fire in some order.

Question: How can I order firing of same type oracle trigger?
Answer:
1. you must consolidate them into a single trigger.
2. All triggers be calls to packaged procedures

Question: Why does Oracle have multiple same-type triggers?
Answer:
The reason goes back to the original implementation of replication in Oracle Database. Materialized view logs used a database trigger to record the data manipulation language (DML) operations that had taken place on a given table.

No comments: