I have the following situation (the real situation is more complex but this simple version illustrates my example) with three tables in a PK-FK relationship.
Table 1 is a parent to Table 2.
Table 2 is a parent to Table 3.
Table 3 has no direct reference to Table 1.
When Table 1 is deleted, Table 2's FK reference to Table 1 is defined as "set null".
When Table 2 is deleted, Table 3's FK reference to Table 2 is defined as "on delete cascade". However in my example I won't even end up firing this because I'm not going to be deleting anything from Table 2.
When Table 2 is updated, a trigger on Table 2 selects data from Table 3 for the same PK-FK relationship between Tables 2 and 3, however it does not perform any DML on Table 3 (mostly because I wanted to just illustrate my issue in a simple context).
DDL:
CREATE TABLE TABLE1
(TABLE1_PK NUMBER NOT NULL,
TABLE1_ATTR1 VARCHAR2(100) NOT NULL)
/
CREATE UNIQUE INDEX TABLE1_PK ON TABLE1 (TABLE1_PK)
/
alter table table1 add CONSTRAINT TABLE1_PK PRIMARY KEY(TABLE1_PK)
/
CREATE TABLE TABLE2
(TABLE2_PK NUMBER NOT NULL,
TABLE1_FK NUMBER NULL,
TABLE2_ATTR1 VARCHAR2(100) NOT NULL)
/
CREATE UNIQUE INDEX TABLE2_PK ON TABLE2 (TABLE2_PK)
/
alter table table2 add CONSTRAINT TABLE2_PK PRIMARY KEY(TABLE2_PK)
/
alter table table2 add CONSTRAINT TABLE2_TO_TABLE1_FK FOREIGN KEY(TABLE1_FK) REFERENCES TABLE1(TABLE1_PK) ON DELETE SET NULL
/
CREATE TABLE TABLE3
(TABLE3_PK NUMBER NOT NULL,
TABLE2_FK NUMBER NOT NULL,
TABLE3_ATTR1 VARCHAR2(100) NOT NULL)
/
CREATE UNIQUE INDEX TABLE3_PK ON TABLE3 (TABLE3_PK)
/
alter table table3 add CONSTRAINT TABLE3_PK PRIMARY KEY(TABLE3_PK)
/
alter table table3 add CONSTRAINT TABLE3_TO_TABLE2_FK FOREIGN KEY(TABLE2_FK) REFERENCES TABLE2(TABLE2_PK) ON DELETE CASCADE
/
insert into table1 values(1,'Record 1 in Table 1')
/
insert into table2 values(10,1,'Record 1 in Table 2')
/
insert into table3 values(100,10,'Record1 in Table 3')
/
commit
/
create or replace trigger table2_aru
after update on table2
for each row
declare
v_table3_rec table3%ROWTYPE;
begin
select *
into v_table3_rec
from table3
where table2_fk = :old.table2_pk;
--
exception
when no_data_found then
null;
when too_many_rows then
null;
end;
/
OK so here it goes:
delete table1;
My expectation is:
1. The deletion of the Table1 record should set TABLE2.TABLE1_FK to null.
2. The update of TABLE2.TABLE1_FK to null will fire the trigger "table2_aru" which selects from table 3 but does nothing else.
What happens? I get this:
ORA-04091: table AMS.TABLE3 is mutating, trigger/function may not see it
ORA-06512: at "AMS.TABLE2_ARU", line 4
ORA-04088: error during execution of trigger 'AMS.TABLE2_ARU'
Why? I am used to mutating table errors when a trigger fires on a table and one is selecting from the same table. But in this case, the trigger is firing on table 2, not table 3, and table 3's data is not even being modified at all, just selected, so why can't I even read table 3 when no DML is being performed on table 3?
Now I'll throw out there something else. Change the management of Table 2-Table 1 FK-PK relationship to not be "on delete set null" but do nothing and instead use a trigger to manage it:
alter table table2 drop constraint table2_to_table1_fk
/
alter table table2 add constraint table2_to_table1_fk foreign key (table1_fk) references table1(table1_pk)
/
create or replace trigger table1_brd
before delete on table1
for each row
begin
update table2
set table1_fk = null
where table1_fk = :old.table1_pk;
end;
/
Now I execute the same delete on table 1:
delete table1;
1 row deleted.
So the only thing that changed is
how table 2's FK to table 1 is cleared out but
nothing changed in the relationship between table 2 and table 3.
So why, when clearing out table 2's FK pointer to table 1 does the database stop me and think that table 3 is mutating when it's done via the FK definition but when done via a trigger, all is well? Oracle bug or...?