Oracle PL/SQL cascading trigger delete not working as expected

Question

I have had a problem with foreign keys. I have created a set of triggers that should cascade deletes through database if a row in table is deleted so that all its children (and children of those children) would be deleted. Here is an example of such trigger.

create or replace trigger trigg_delet_child before delete on PARENT for 
each row
begin
    delete from CHILD where foreign_key_parent_id = :old.id_parent;
end;

I have a set of such triggers on each table. I have assumed that since the trigger should fire before the delete statement is executed, and therefore passes the torch to lower and lower tables until we finally hit item without children and begin delete from there and work our way up.

This does not appear to be the case, as a foreign_key constraint is rather understandably violated, because apparently i misunderstood how BEFORE works. Is there a way to sidestep this issue without specifying that the foreign_key constraint should cascade on delete?

What's wrong with using a foreign key that's defined with on delete cascade? — a_horse_with_no_name
1) It will not work on table that has foreign keys from two tables that are higher on the totem pole. Or at least, so i was told. 2) This work is for an exercise in class. The priority is on practicing triggers, procedures and functions. I can solve this problem easily via making a delete procedures and call them in the right order, but i wanted to give triggers a try. — Vojta Martinec
Of course you will need to do that for all foreign keys in the "totem pole" — a_horse_with_no_name
Then let us assume that "on delete cascade" constraint does not exist. Am i right to guess that i will need to use procedures/functions, or is there some kind of way to actually fire the aforementioned trigger? — Vojta Martinec

Stephen Caggiano Stephen Caggiano · Accepted Answer · 2018-12-14T20:54:08

I gave this a shot in the below sample code and it works. Do you have other triggers on the tables? or tables with self-referencing FKs?

    Create table a (a_id number primary key, 
                a_text varchar2(10));

Create table b (b_id number primary key, 
                a_id number REFERENCES a (a_id),
                b_text varchar2(10));

Create table c (c_id number primary key, 
                b_id number REFERENCES b (b_id), 
                c_text varchar2(10));



Insert into a values(1 , 'A');
Insert into b values(11, 1, 'B');
Insert into c values(101, 11, 'C');
commit;

-- Generates an error
Delete from a;

create or replace trigger A_DEL before delete on a for 
each row
begin
    delete from B where A_id = :old.A_ID;
end;
/

create or replace trigger B_DEL before delete on B for 
each row
begin
    delete from C where B_id = :old.B_ID;
end;
/

-- Works
Delete from a;
COMMIT;

Oracle PL/SQL cascading trigger delete not working as expected

1 Answers