Question: I have two insert triggers on
my table, a row level trigger and a regular DML trigger.
Upon insert, which trigger will fire first?
Answer: The Oracle documentation is ambiguous
about the firing order of triggers.
Oracle 11g PL/SQL and beyond will allow you to specify trigger firing order.
In 11g and beyond there is a "follows" and
"precedes" syntax that you can use to control the order in
which triggers are executed:
-- This set trigger order firing sequence for one that
follows
-- trigger_test_2
create or replace
trigger
trigger_test
before insert on
mytab
for each row
follows trigger_test_2
begin
. . .
end;/
create or replace
trigger
trigger_test_2
before insert on
mytab
for each row
precedes trigger_test
begin
. . .
end;/
Another
sure-fire way to guarantee the firing order of triggers is
to encapsulate then into stored procedure and attached the
stored procedure as a single trigger. This will
guarantee the firing sequence of triggers.
Displaying the firing order of triggers
There is
also an dba_trigger_ordering dictionary view that
displays the sequence that triggers are fired when they are
defined with the "follows" or "precedes" clause:
select
trigger_owner,
trigger_name,
referenced_trigger_owner,
referenced_trigger_name,
ordering_type
from
sys.dba_trigger_ordering;
