Referential Integrity. on delete set null
531333Sep 1 2006 — edited Sep 1 2006I have run across something that I can't explain, and I am wondering if it is a bug, or if there is a reason this is happening. The situation is that I have a child table that has a foreign key constraint on a parent table defined with on delete set null. This functionality is working as expected. However, on the child table, for business reasons, I have a trigger that fails if a deletion is tried. Now here is my question, if this trigger is row level everything works just fine; however, if it is a statement level trigger it gets fired and prevents the deletion on the parent and the update in the child. I am not sure why that is happening, and I would really like to use the statement level trigger. Does anybody have any insight? Here is a concrete example:
create table A (aId number(9) primary key);
create table B (bId number(9) primary key, aId number(9),
constraint aIdFK foreign key(aId) references A(aId) on delete set null);
create or replace trigger DenyDelete before delete on B
for each row
begin
raise_application_error(-20001, 'Cannot delete from table B');
end;
/
-- test it out
insert into A values(1);
insert into B values(1, 1);
delete from B; -- should get the application error from the trigger
delete from A; -- works just fine as row level. fails if the trigger is statement level
Any insights would be greatly appreciated