Re: On the order of triggers firing.

Well, how about giving it a try?

Make table A and B look like: create table A|B( n1 number ,d1 date )

Create your triggers as UPDATE instead of delete and put in code that updates column 'd1' with SYSDATE and put in lots of DBMS_OUTPUT lines, like the following, in each of the triggers.

DBMS_OUTPUT.PUT_LINE( 'Running A-TRIG-BDEL at ' || to_char( SYSDATE ,'YYYY-DD-MM HH24:MI:SS' ) );
DBMS_OUTPUT.PUT_LINE( 'Running A-TRIG-SDEL at ' || to_char( SYSDATE ,'YYYY-DD-MM HH24:MI:SS' ) ); BDEL = before trig, ATRIG = after trig and STRIG = stmt trig.

Be sure to use 'set serveroutput on' in SQL*Plus so that you can see the output from DBMS_OUTPUT.

I would tell you the answer directly, but after you get done messing with all of this for a couple of days, you have a really clear understanding of just how triggers fit together.

Finally, read up of triggers and get a clear understand of when Oracle fires a statement level trigger. Test what you have read with a simple trio of triggers on Table C using STMT, BEFORE and AFTER triggers. Be sure to use lots of DBMS_OUTPUT.PUT_LINE( '...' ).

Lastly, actual output from the DBMS_OUTPUT procedure, often, doesn't appear until after the entire SQL statement+triggers is completed. So in long running processes don't be too alarmed if output doesn't immediately appear when you expected it to.

Mick Davies wrote in message <01bcdb36$c0f8a990$84e16acc_at_mdavies>...
>Greetings from an Oracle newbie.
>Here is my question:
>Assume two tables: TableA, TableB.
>Also, assume the following triggers:
>
>Trig1: Statement-level trigger before delete on TableA
>Trig2: Row-level trigger after delete on TableA
>Trig3: Statement-level trigger after delete on TableA
>Trig4: Statement-level trigger before delete on TableB
>Trig5: Row-level trigger after delete on TableB
>Trig6: Statement-level trigger after delete on TableB
>
>Finally, assume Trig2 issues a delete statement on one or more records in
>TableB
>
>The behaviour I would like to see is :
>Triggers fired in the following chronological order:
>
>Trig1
>Repeat for each row deleted in Table A
> Trig2
> Trig4
> Repeat for each record deleted in TableB by statement in Trig2
> Trig5
> end repeat
> Trig3
>end repeat
>Trig6
>
>Question: Is this in fact the correct chronological order, and can I rely
>on it?
>
>Thanks in advance for the answer!
>
>Mick
>
>
>
>
>
Received on Sat Oct 18 1997 - 00:00:00 CDT