Skip to Main Content
  • Questions

  • Cascade delete doesnt fire the trigger of the child table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Analyst.

Asked: June 09, 2016 - 12:11 pm UTC

Last updated: June 15, 2016 - 1:02 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom


I have two tables A and B .
Table A is parent to B (One to many)
B has the option Cascade delete whenever a delete occurs on A.

There is a trigger on table B which executes if a delete/update/insert is done directly on the table.
But the trigger does not fire if I delete a record on A.

Is this a normal behaviour?




and Connor said...

Here is my testcase ... I await yours in anticipation :-)


SQL> create table par ( p int primary key );

Table created.

SQL> insert into par values (1);

1 row created.

SQL>
SQL> create table chd ( c int primary key, p int references par (p) on delete cascade );

Table created.

SQL>
SQL> insert into chd values (1,1);

1 row created.

SQL> insert into chd values (2,1);

1 row created.

SQL> insert into chd values (3,1);

1 row created.

SQL> insert into chd values (4,1);

1 row created.

SQL> insert into chd values (5,1);

1 row created.

SQL>
SQL> create or replace
  2  trigger trg_chd
  3  after delete on chd
  4  for each row
  5  begin
  6    dbms_output.put_line('Deleted '||:old.c);
  7  end;
  8  /

Trigger created.

SQL>
SQL> set serverout on
SQL> delete from par;
Deleted 1
Deleted 2
Deleted 3
Deleted 4
Deleted 5

1 row deleted.

SQL>


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Analyst, June 10, 2016 - 5:16 am UTC

Hello,

Thanks for your prompt reply

I did something quite similar

create or replace TRIGGER "TRIG_DELETE_A"
FOR DELETE ON A COMPOUND TRIGGER
DETAILID NUMBER(20);
after EACH ROW IS
BEGIN
DETAILID := :OLD.DETAIL_ID;
DELETE FROM B WHERE DETAIL_ID= DETAILID;

END after EACH ROW;
END;

It was going to work :) but unfortunately in the trigger that is fired on Table B (im posting part of the trigger)

create or replace TRIGGER "TRIG_UPDATE_B"
FOR DELETE OR INSERT OR UPDATE of NUMBER_OF_PIECES,WEIGHT,VOLUME_AMT ON B COMPOUND TRIGGER
.....
.....
AFTER statement IS
BEGIN

SELECT ACTION_PAGE INTO ACTION_FLAG FROM A WHERE DETAIL_ID = CONSIGNMENT_ID;
.......
.......
END After statement;
END;
the select ends in table mutating error since im accessing a field from the parent table.

This trigger was written well before the "TRIG_DELETE_A" so
now im stuck.
I have seen various solutions including autonomous etc
but its not safe

You have any idea what can be done.
Connor McDonald
June 10, 2016 - 11:42 am UTC

From what I can interpret there you've got:

1) a trigger on A that deletes from B
2) a delete trigger on B that wants to look at A

So on the assumption that you only want (2) to apply when the delete is NOT coming from (1), you could use a package variable as a flag.

Something like:

Trigger 1:

pkg.fire_B_trigger := false;
delete from B ...
pkg.fire_B_trigger := true;

Trigger 2:

if not pkg.fire_B_trigger then
-- processing as before
end if;

with some appropriate error handling to ensure the package variable cannot be left incorrectly set.

One example on 12.1.0.2.0

lh, June 10, 2016 - 9:04 am UTC

Hi

Well, If I may participate when You asked for an example.

drop table lh_r;
drop table lh_p;
drop table lh_s;

create table lh_s
(s_seq number(20),
s_code varchar2(20))
partition by hash (s_seq)
partitions 16;
create unique index lh_s_pk on lh_s(s_seq) local;
ALTER TABLE lh_s ADD (
CONSTRAINT lh_s_pk
PRIMARY KEY
(S_SEQ)
USING INDEX LOCAL);

create table lh_p
(p_seq number(20),
s_seq number(20),
p_code varchar2(20))
partition by hash (p_seq)
partitions 16;
create unique index lh_p_pk on lh_p(p_seq) local;
create index lh_p_in1 on lh_p(s_seq) ;

ALTER TABLE lh_p ADD (
CONSTRAINT lh_p_pk
PRIMARY KEY
(p_SEQ)
USING INDEX LOCAL);

ALTER TABLE lh_p ADD (
CONSTRAINT lh_p_fk_s_seq
FOREIGN KEY (S_SEQ)
REFERENCES lh_s (s_seq)
ON DELETE CASCADE);

create table lh_r (
r_id varchar2(20),
r_i number(20),
primary key (r_id, r_i))
organization index;

CREATE OR REPLACE TRIGGER lh_p_trg
AFTER INSERT
ON lh_p REFERENCING NEW AS NEW OLD AS OLD
for each row
begin
insert into lh_r(r_id, r_i) values (:new.p_code, :new.p_seq);
end;
/
CREATE OR REPLACE TRIGGER lh_p_trg_del
AFTER delete
ON lh_p REFERENCING NEW AS NEW OLD AS OLD
for each row
begin
delete from lh_r where (r_id, r_i) in ((:old.p_code, :old.p_seq));
end;
/

insert into lh_s(s_seq, s_code) values (1,'cat');
insert into lh_s(s_seq, s_code) values (2,'dog');

insert into lh_p(p_seq, s_seq, p_code) values (3,1,'cat');
insert into lh_p(p_seq, s_seq, p_code) values (4,2,'dog');
--
-- this should fail
--
insert into lh_p(p_seq, s_seq, p_code) values (5,6,'bat');

select * from lh_s;
2 dog
1 cat
select * from lh_p;
3 1 cat
4 2 dog
select * from lh_r;
cat 3
dog 4

delete from lh_s s
where s.s_seq in
(select p.s_seq from lh_p p
where p.p_seq in (select r.r_i
from lh_r r
where r.r_id='dog'
)
);
commit;
--
-- row is removed
--
select * from lh_s;
1 cat
--
-- but cascading delete is not working
--
select * from lh_p;
3 1 cat
4 2 dog
select * from lh_r;
cat 3
dog 4

--
-- now with little simpler test case.
--
delete from lh_s s
where s.s_seq in
(select p.s_seq from lh_p p
where p.p_seq in (3
)
);
commit;
select * from lh_s;
no rows selected
select * from lh_p;
4 2 dog
select * from lh_r;
dog 4
--
-- and you can notice, that cascading deletes have worked in this case.
--
commit;


Actually we have made a support case of this.


Connor McDonald
June 10, 2016 - 12:02 pm UTC

True...that is definitely worth a chat with Support about. But its possible more a query transformation or optimization thing here getting in the way of the correct processing. For example, if I tweak the problem subquery


SQL> create table lh_s
  2  (s_seq number(20),
  3   s_code varchar2(20))
  4  partition by hash (s_seq)
  5  partitions 4;

Table created.

SQL>
SQL> create unique index lh_s_pk on lh_s(s_seq) local;

Index created.

SQL>
SQL> ALTER TABLE lh_s ADD (
  2  CONSTRAINT lh_s_pk
  3  PRIMARY KEY
  4  (S_SEQ)
  5  USING INDEX LOCAL);

Table altered.

SQL>
SQL> create table lh_p
  2  (p_seq number(20),
  3   s_seq number(20),
  4   p_code varchar2(20))
  5  partition by hash (p_seq)
  6  partitions 4;

Table created.

SQL>
SQL> create unique index lh_p_pk on lh_p(p_seq) local;

Index created.

SQL>
SQL> create index lh_p_in1 on lh_p(s_seq) ;

Index created.

SQL>
SQL> ALTER TABLE lh_p ADD (
  2  CONSTRAINT lh_p_pk
  3  PRIMARY KEY
  4  (p_SEQ)
  5  USING INDEX LOCAL);

Table altered.

SQL>
SQL> ALTER TABLE lh_p ADD (
  2  CONSTRAINT lh_p_fk_s_seq
  3  FOREIGN KEY (S_SEQ)
  4  REFERENCES lh_s (s_seq)
  5  ON DELETE CASCADE);

Table altered.

SQL>
SQL>
SQL> create table lh_r (
  2  r_id varchar2(20),
  3  r_i number(20),
  4  primary key (r_id, r_i))
  5  organization index;

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER lh_p_trg
  2  AFTER INSERT
  3  ON lh_p REFERENCING NEW AS NEW OLD AS OLD
  4  for each row
  5  begin
  6    insert into lh_r(r_id, r_i) values (:new.p_code, :new.p_seq);
  7  end;
  8  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER lh_p_trg_del
  2  AFTER delete ON lh_p REFERENCING NEW AS NEW OLD AS OLD
  3  for each row
  4  begin
  5    delete from lh_r where (r_id, r_i) in ((:old.p_code, :old.p_seq));
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> insert into lh_s(s_seq, s_code) values (1,'cat');

1 row created.

SQL> insert into lh_s(s_seq, s_code) values (2,'dog');

1 row created.

SQL>
SQL> insert into lh_p(p_seq, s_seq, p_code) values (3,1,'cat');

1 row created.

SQL> insert into lh_p(p_seq, s_seq, p_code) values (4,2,'dog');

1 row created.

SQL>
SQL> select * from
  2
SQL> --
SQL> -- this should fail
SQL> --
SQL> --insert into lh_p(p_seq, s_seq, p_code) values (5,6,'bat');
SQL>
SQL> select * from lh_s;

     S_SEQ S_CODE
---------- --------------------
         2 dog
         1 cat

2 rows selected.

SQL> select * from lh_p;

     P_SEQ      S_SEQ P_CODE
---------- ---------- --------------------
         3          1 cat
         4          2 dog

2 rows selected.

SQL> select * from lh_r;

R_ID                        R_I
-------------------- ----------
cat                           3
dog                           4

2 rows selected.

SQL>
SQL> delete from lh_s s
  2  where s.s_seq in
  3    (  select p.s_seq
  4       from lh_p p
  5       where p.p_seq in (select r.r_i
  6                         from lh_r r
  7                         where r.r_id='dog'
  8                         and rownum >0     <<====
  9                         )
 10  );

1 row deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from lh_s;

     S_SEQ S_CODE
---------- --------------------
         1 cat

1 row selected.

SQL> select * from lh_p;

     P_SEQ      S_SEQ P_CODE
---------- ---------- --------------------
         3          1 cat

1 row selected.

SQL> select * from lh_r;

R_ID                        R_I
-------------------- ----------
cat                           3

1 row selected.

SQL>


btw - thanks for the test case. Makes life so much easier for us.


Only in case of Partitioned Tables - in 12c and 11g not with 10g

Rajeshwaran, Jeyabal, June 12, 2016 - 1:26 pm UTC 

set feedback off
drop table t3 purge;
drop table t2 purge;
drop table t1 purge;
create table t1(x int, object_name varchar2(30),
 constraint t1_pk primary key(x) );
create table t2(x int, y int, object_name varchar2(30),
 constraint t2_pk primary key(x) ,
 constraint t2_fk foreign key(y) references t1 on delete cascade);
create index t2_idx on t2(y); 
create table t3(x int, y int,
 constraint t3_pk primary key(x,y)) 
 organization index ;
create or replace trigger t2_trig
before insert or delete on t2
for each row 
begin 
 if inserting then 
  insert into t3(x,y) values(:new.x,:new.y) ;
 elsif deleting then 
  delete from t3 where x = :old.x and y = :old.y;
 end if;
end;
/ 
insert into t1 values(1,'Text1');
insert into t1 values(2,'Text2');
insert into t2 values(3,1,'Text1');
insert into t2 values(4,2,'Text2');
commit;
set feedback on


In case of non-partitioned Table, looks OK.

demo@ORA12C>
demo@ORA12C> select * from t1;

         X OBJECT_NAME
---------- ------------------------------
         1 Text1
         2 Text2

2 rows selected.

demo@ORA12C> select * from t2;

         X          Y OBJECT_NAME
---------- ---------- ------------------------------
         3          1 Text1
         4          2 Text2

2 rows selected.

demo@ORA12C> select * from t3;

         X          Y
---------- ----------
         3          1
         4          2

2 rows selected.

demo@ORA12C> delete from t1
  2  where t1.x in ( select t2.y from t2
  3     where t2.x in ( select x from t3
  4             where y = 2) );

1 row deleted.

demo@ORA12C> select * from t1;

         X OBJECT_NAME
---------- ------------------------------
         1 Text1

1 row selected.

demo@ORA12C> select * from t2;

         X          Y OBJECT_NAME
---------- ---------- ------------------------------
         3          1 Text1

1 row selected.

demo@ORA12C> select * from t3;

         X          Y
---------- ----------
         3          1

1 row selected.

demo@ORA12C>


In case of partitioned Tables.

set feedback off
drop table t3 purge;
drop table t2 purge;
drop table t1 purge;
create table t1(x int, object_name varchar2(30) )
 partition by hash(x)
 partitions 4;
alter table t1 add constraint t1_pk
primary key(x) using index (create unique index t1_pk 
  on t1(x) local );  
create table t2(x int, y int, object_name varchar2(30))
partition by hash(x)
partitions 4;
alter table t2 add constraint t2_pk
primary key(x) using index ( create unique index t2_pk
 on t2(x) local );
create index t2_idx on t2(y);
alter table t2 add constraint t2_fk
foreign key(y) references t1
on delete cascade; 
create table t3(x int, y int,
 constraint t3_pk primary key(x,y)) 
 organization index ;
create or replace trigger t2_trig
before insert or delete on t2
for each row 
begin 
 if inserting then 
  insert into t3(x,y) values(:new.x,:new.y) ;
 elsif deleting then 
  delete from t3 where x = :old.x and y = :old.y;
 end if;
end;
/
insert into t1 values(1,'Text1');
insert into t1 values(2,'Text2');
insert into t2 values(3,1,'Text1');
insert into t2 values(4,2,'Text2');
commit;
set feedback on


look problem now.

demo@ORA12C> select * from t1;

         X OBJECT_NAME
---------- ------------------------------
         2 Text2
         1 Text1

2 rows selected.

demo@ORA12C> select * from t2;

         X          Y OBJECT_NAME
---------- ---------- ------------------------------
         3          1 Text1
         4          2 Text2

2 rows selected.

demo@ORA12C> select * from t3;

         X          Y
---------- ----------
         3          1
         4          2

2 rows selected.

demo@ORA12C> delete from t1
  2  where t1.x in ( select t2.y from t2
  3     where t2.x in ( select x from t3
  4             where y = 2) );

1 row deleted.

demo@ORA12C> select * from t1;

         X OBJECT_NAME
---------- ------------------------------
         1 Text1

1 row selected.

demo@ORA12C> select * from t2;

         X          Y OBJECT_NAME
---------- ---------- ------------------------------
         3          1 Text1
         4          2 Text2

2 rows selected.

demo@ORA12C> select * from t3;

         X          Y
---------- ----------
         3          1
         4          2

2 rows selected.

demo@ORA12C>
demo@ORA12C> commit;

Commit complete.

demo@ORA12C>


The strange thing is even during commit, constraint is not validated and no errors.

Able to reproduce this behavior in 11g(11.2.0.4) and 12c(12.1.0.2) but not with 10g(10.2.0.5)

demo@ORA10G> set feedback off
demo@ORA10G> drop table t3 purge;
demo@ORA10G> drop table t2 purge;
demo@ORA10G> drop table t1 purge;
demo@ORA10G> create table t1(x int, object_name varchar2(30) )
  2     partition by hash(x)
  3     partitions 4;
demo@ORA10G> alter table t1 add constraint t1_pk
  2  primary key(x) using index (create unique index t1_pk
  3             on t1(x) local );
demo@ORA10G> create table t2(x int, y int, object_name varchar2(30))
  2  partition by hash(x)
  3  partitions 4;
demo@ORA10G> alter table t2 add constraint t2_pk
  2  primary key(x) using index ( create unique index t2_pk
  3     on t2(x) local );
demo@ORA10G> create index t2_idx on t2(y);
demo@ORA10G> alter table t2 add constraint t2_fk
  2  foreign key(y) references t1
  3  on delete cascade;
demo@ORA10G> create table t3(x int, y int,
  2     constraint t3_pk primary key(x,y))
  3     organization index ;
demo@ORA10G> create or replace trigger t2_trig
  2  before insert or delete on t2
  3  for each row
  4  begin
  5     if inserting then
  6             insert into t3(x,y) values(:new.x,:new.y) ;
  7     elsif deleting then
  8             delete from t3 where x = :old.x and y = :old.y;
  9     end if;
 10  end;
 11  /
demo@ORA10G> insert into t1 values(1,'Text1');
demo@ORA10G> insert into t1 values(2,'Text2');
demo@ORA10G> insert into t2 values(3,1,'Text1');
demo@ORA10G> insert into t2 values(4,2,'Text2');
demo@ORA10G> commit;
demo@ORA10G> set feedback on
demo@ORA10G>
demo@ORA10G>
demo@ORA10G> select * from t1;

         X OBJECT_NAME
---------- ------------------------------
         2 Text2
         1 Text1

2 rows selected.

demo@ORA10G> select * from t2;

         X          Y OBJECT_NAME
---------- ---------- ------------------------------
         3          1 Text1
         4          2 Text2

2 rows selected.

demo@ORA10G> select * from t3;

         X          Y
---------- ----------
         3          1
         4          2

2 rows selected.

demo@ORA10G> delete from t1
  2  where t1.x in ( select t2.y from t2
  3     where t2.x in ( select x from t3
  4             where y = 2) );

1 row deleted.

demo@ORA10G> select * from t1;

         X OBJECT_NAME
---------- ------------------------------
         1 Text1

1 row selected.

demo@ORA10G> select * from t2;

         X          Y OBJECT_NAME
---------- ---------- ------------------------------
         3          1 Text1

1 row selected.

demo@ORA10G> select * from t3;

         X          Y
---------- ----------
         3          1

1 row selected.

demo@ORA10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.

demo@ORA10G>

Analyst, June 14, 2016 - 11:26 am UTC

Thx for all the good ideas.

the pkg variable looks like would the job


Connor McDonald
June 15, 2016 - 1:02 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library