Re: Delete trigger order of execution with on_delete_cascade

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 25 Mar 2006 08:33:35 +0100
Message-ID: <4424f24e$0$21522$626a54ce@news.free.fr>

<mark.catchpole_at_btinternet.com> a écrit dans le message de news: 1143210994.634341.209680_at_j33g2000cwa.googlegroups.com...
| Hi,
|
| I am trying to establish the order of execution of delete triggers when
| on-delete-cascade is set on my foreign key.
|
| If I have three tables T1, T2 and T3, all of which have before and
| after delete triggers defined, how will the be executed when I delete a
| row from T1 with the delete cascade? Are they nested thus:
|
| T1_BEFORE_DELETE fires
| T2_BEFORE_DELETE fires
| T3_BEFORE_DELETE fires
| T3_AFTER_DELETE fires
| T2_AFTER_DELETE fires
| T1_AFTER_DELETE fires
|
| or are they done in a more linear fashion? like:
|
| T1_BEFORE_DELETE fires
| T1_AFTER_DELETE fires
| then
| T2_BEFORE_DELETE fires
| T2_AFTER_DELETE fires
| then
| T3_BEFORE_DELETE fires
| T3_AFTER_DELETE fires
|
| Or is it done in some other non-deterministic way???
| Any help much appreciated.
| Thanks
| Mark
|

SQL> create table t1 (col1 number primary key);

Table created.

SQL> create table t2 (col2a number primary key,

  2                   col2b number references t1 on delete cascade);

Table created.

SQL> create table t3 (col3a number primary key,

  2                   col3b number references t2 on delete cascade);

Table created.

SQL>
SQL> create or replace trigger t1_bds
  2 before delete on t1
  3 begin
  4 dbms_output.put_line('t1 before');   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t1_bdr
  2 before delete on t1 for each row
  3 begin
  4 dbms_output.put_line('t1 before for each row '||:old.col1);   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t1_ads
  2 after delete on t1
  3 begin
  4 dbms_output.put_line('t1 after');   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t1_adr
  2 after delete on t1 for each row
  3 begin
  4 dbms_output.put_line('t1 after for each row '||:old.col1);   5 end;
  6 /

Trigger created.

SQL>
SQL> create or replace trigger t2_bds
  2 before delete on t2
  3 begin
  4 dbms_output.put_line('t2 before');   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t2_bdr
  2 before delete on t2 for each row
  3 begin
  4 dbms_output.put_line(
  5 't2 before for each row '||:old.col2a||' '||:old.col2b);   6 end;
  7 /

Trigger created.

SQL> create or replace trigger t2_ads
  2 after delete on t2
  3 begin
  4 dbms_output.put_line('t2 after');   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t2_adr
  2 after delete on t2 for each row
  3 begin
  4 dbms_output.put_line(
  5 't2 after for each row '||:old.col2a||' '||:old.col2b);   6 end;
  7 /

Trigger created.

SQL>
SQL> create or replace trigger t3_bds
  2 before delete on t3
  3 begin
  4 dbms_output.put_line('t3 before');   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t3_bdr
  2 before delete on t3 for each row
  3 begin
  4 dbms_output.put_line(
  5 't3 before for each row '||:old.col3a||' '||:old.col3b);   6 end;
  7 /

Trigger created.

SQL> create or replace trigger t3_ads
  2 after delete on t3
  3 begin
  4 dbms_output.put_line('t3 after');   5 end;
  6 /

Trigger created.

SQL> create or replace trigger t3_adr
  2 after delete on t3 for each row
  3 begin
  4 dbms_output.put_line(
  5 't3 after for each row '||:old.col3a||' '||:old.col3b);   6 end;
  7 /

Trigger created.

SQL> insert into t1 values (1.1);

1 row created.

SQL> insert into t1 values (1.2);

1 row created.

SQL> insert into t2 values (2.1,1.1);

1 row created.

SQL> insert into t2 values (2.2,1.1);

1 row created.

SQL> insert into t2 values (2.3,1.2);

1 row created.

SQL> insert into t2 values (2.4,1.2);

1 row created.

SQL> insert into t3 values (3.1,2.1);

1 row created.

SQL> insert into t3 values (3.2,2.1);

1 row created.

SQL> insert into t3 values (3.3,2.2);

1 row created.

SQL> insert into t3 values (3.4,2.2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

      COL1

---

       1.1
       1.2

2 rows selected.

SQL> select * from t2;

     COL2A COL2B
---------- ----------

       2.1        1.1
       2.2        1.1
       2.3        1.2
       2.4        1.2

4 rows selected.

SQL> select * from t3;

     COL3A COL3B
---------- ----------

       3.1        2.1
       3.2        2.1
       3.3        2.2
       3.4        2.2

4 rows selected.

SQL> delete t1;
t3 before
t2 before
t1 before

t1 before for each row 1.1
t2 before for each row 2.1 1.1
t3 before for each row 3.1 2.1

t3 after for each row 3.1 2.1
t3 before for each row 3.2 2.1
t3 after for each row 3.2 2.1
t2 after for each row 2.1 1.1
t2 before for each row 2.2 1.1
t3 before for each row 3.3 2.2
t3 after for each row 3.3 2.2
t3 before for each row 3.4 2.2

t3 after for each row 3.4 2.2
t2 after for each row 2.2 1.1
t1 after for each row 1.1

t1 before for each row 1.2
t2 before for each row 2.3 1.2
t2 after for each row 2.3 1.2
t2 before for each row 2.4 1.2
t2 after for each row 2.4 1.2
t1 after for each row 1.2
t3 after
t2 after
t1 after

2 rows deleted.

Regards
Michel Cadot Received on Sat Mar 25 2006 - 01:33:35 CST