Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Referential Integrity. on delete set null

531333Sep 1 2006 — edited Sep 1 2006
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2006
Added on Sep 1 2006
2 comments
570 views