Oraclue

Oracle internals, debugging and undocumented features

Change on firing order on before trigger and FK delete cascade in 11g

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.

Advertisements

2 responses to “Change on firing order on before trigger and FK delete cascade in 11g

  1. dombrooks December 11, 2009 at 3:16 pm

    In other words, before 11g, integrity constraints fired before triggers.

    From 11g onwards, triggers fire before integrity constraints.

  2. dombrooks December 11, 2009 at 5:31 pm

    Sorry – that comment was wrong, wasn’t it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: