Miladin Modrakovic's Blog: Oraclue

Order of execution for 10g :  Child trigger fire before master trigger.

Order of execution for 11g:   Master trigger fire before  Child.

According to Oracle  starting with version 8.1   they collect all  before statement triggers and fire before DML for all tables involved with delete with cascade.Order of execution is undefined .Same for multiple triggers on the same table.Starting with 11g we can guarantee execution order for triggers defined with the same timing  using FOLLOWS clause.

So all these changes on triggers in 11g introduced this behavior.Problem is if your application runs on 10g and it  is using triggers and fk with cascade delete and you are upgrading to 11g.Something to think before upgrade..

Test case 10g:

SQL> alter session set current_schema=scott;

Session altered.

SQL> drop table child;
drop table master;

create table master (pk number(4));

alter table master add constraint pk_master primary key (PK);

create or replace trigger master_bd
before delete on master
begin
dbms_output.put_line(‘Master trigger before fired’);
end child_bd;
/
create table child (fk number(4));
alter table child add constraint fk_child foreign key (FK) references master  (PK) on delete cascade;
create or replace trigger child_bd
before delete on child
begin   dbms_output.put_line(‘Child trigger before fired’);
end child_bd;
/
insert into master values (1);
insert into master values (2);
insert into master values (3);
insert into child values (1);
insert into child values (1);
insert into child values (2);
insert into child values (2);
insert into child values (3);
commit;
Table dropped.

SQL>
Table dropped.

SQL> SQL>
Table created.

SQL> SQL>
Table altered.

SQL> SQL>   2    3    4    5    6
Trigger created.

SQL> SQL> SQL>
Table created.

SQL>
Table altered.

SQL> SQL> SQL>   2    3    4    5
Trigger created.

SQL> SQL> SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.

SQL> set serveroutput on
delete master where pk = 2;
commit;

SQL>  Child trigger before fired
            Master trigger before fired

1 row deleted.

Order of execution:  first fired child trigger and then master trigger.

Let’s see 11g:

SQL> alter session set current_schema=scott;

Session altered.

SQL> drop table child;
drop table master;

create table master (pk number(4));

alter table master add constraint pk_master primary key (PK);

create or replace trigger master_bd
before delete on master
begin
dbms_output.put_line(‘Master trigger before fired’);
end child_bd;
/
create table child (fk number(4));
alter table child add constraint fk_child foreign key (FK) references master  (PK) on delete cascade;
create or replace trigger child_bd
before delete on child
begin   dbms_output.put_line(‘Child trigger before fired’);
end child_bd;
/
insert into master values (1);
insert into master values (2);
insert into master values (3);
insert into child values (1);
insert into child values (1);
insert into child values (2);
insert into child values (2);
insert into child values (3);
commit;
Table dropped.

SQL>
Table dropped.

SQL> SQL>
Table created.

SQL> SQL>
Table altered.

SQL> SQL>   2    3    4    5    6
Trigger created.

SQL> SQL> SQL>
Table created.

SQL>
Table altered.

SQL> SQL> SQL>   2    3    4    5
Trigger created.

SQL> SQL> SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.

SQL> set serveroutput on
delete master where pk = 2;
commit;

 SQL> Master trigger before fired
             Child trigger before fired

1 row deleted.

SQL>

Commit complete.

Master trigger fired before child trigger.Oposite from 10g.