Mutating Table Problems with DELETE CASCADE
Overview
This week, we suddenly had a "strange" error message in one of our applications:
ORA-04091: table APP is mutating,
trigger/function may not see it
ORA-06512: at "TRG_GET_ID_APP", line 4
ORA-04088: error during execution of trigger 'TRG_GET_ID_APP'.
We studied the "Mutating Table" Problem and found the following explanations in the Oracle8i Application Developer's Guide.
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress.
For all row triggers, that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
The SQL statements of a trigger cannot read from (query) or modify a
mutating table of the triggering statement.
Example 1
Select in a mutating table from a row trigger (Tested on Oracle 8.1.7)
We want to explain this situation on an example. We have two tables "A" and "B". "A" is the master table and "B" the detail table. We specified a foreign key between "B" and "A" with the CASCADE DELETE option.
Here are the CREATE statements
drop table B;
drop table A;create table A (
ida number not null,
vala varchar2(10),
primary key(ida));create table B (
idb number,
valb varchar2(10),
foreign key (idb) references A (ida) on delete cascade)
/create or replace trigger b_br
after delete on B
for each row
declare
n integer;
begin
select count(*) into n from A;
dbms_output.put_line('there are ' || n || ' rows in A');
dbms_output.put_line('after statment on B');
dbms_output.new_line;
end;
/insert into A values(1,'Table A');
insert into A values(2,'Table A');
insert into B values(1,'Table B');
insert into B values(1,'Table B');
commit;set serveroutput on;
delete from A where idA = 1;
ERROR at line 1:
ORA-04091: table SCOTT.A is mutating, trigger/function may not see
ORA-06512: at "SCOTT.B_BR", line 4
ORA-04088: error during execution of trigger 'SCOTT.B_BR'
Notice that the SQL statement ( "select count(*) into n from A" ) is run for the first row of the table, and then the AFTER row trigger B_BR is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table A. However, because the table A is mutating due to the CASCADE DELETE foreign key, this query is not allowed by Oracle. If attempted, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
Solution: Use statement trigger instead of row trigger
If you delete the line "FOR EACH ROW" from the trigger above, then the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data.
SQL> delete from A where idA = 1;
there are 1 rows in A
after statment on B
1 row deleted.SQL> select count(*) from B;
COUNT(*)
----------
0It is not always possible to change the row trigger to a statement trigger. In this case, the oracle manual proposes the following solution:
Example 2
Cascading Update with Oracle 8i
Before Oracle8i, there was a "constraining error" that prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint. As of Oracle8i, there is no constraining error. In addition, checking of the foreign key is deferred until at least the end of the parent statement. The mutating error still prevents the trigger from reading or modifying the table that the parent statement is modifying.
This allows most foreign key constraint actions to be implemented via their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily.
However there is one problem with a multirow update - let's demonstrate this on an example.
create table P (
p1 number not null,
primary key (p1));create table F (
f1 number,
foreign key (f1) references P (p1) on delete cascade);create trigger pu_ar
after update on p
for each row
begin
if (:new.p1 != :old.p1) then
update f
set f1 = :new.p1
where f1 = :old.p1;
end if;
end;
/insert into p values (1);
insert into p values (2);
insert into p values (3);
insert into f values (1);
insert into f values (2);
insert into f values (3);
commit;update p set p1 = p1+1;
commit;SQL> select * from p;
P1
----------
2 <===== OK
3 <===== OK
4 <===== OKSQL> select * from f;
F1
----------
4 <===== Wrong !
4 <===== Wrong !
4 <===== Wrong !The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, you must forbid to update to an existing primary key, if this primary key have any childs, this can be accomplished with the following trigger.
create or replace trigger pu_ar
after update on P
for each rowdeclare
echildexists exception;-- check if child table have child records with this new
-- key, this is not allowed.cursor curs_exists is
select 'x'
from F
where f1 = :new.p1;rtf curs_exists%rowtype;
begin
if(:new.p1 <> :old.p1) thenopen curs_exists;
fetch curs_exists into rtf;if (curs_exists%found) then
close curs_exists;
raise echildexists;
end if;close curs_exists;
update f
set f1 = :new.p1
where f1 = :old.p1;
end if;exception
when echildexists then
raise_application_error (
-20102,'error: this primary key: '
||to_char(:new.p1)
||' exists and has child rows in f, this triggers
again an update and so on ...');
end pu_ar;
/Test the update
SQL> update p set p1 = p1+1;
ERROR at line 1:
ORA-20102: error: this primary key: 2 exists and has child
rows in f, this triggers again an update and so on ...
ORA-06512: at "SCOTT.PU_AR", line 34
ORA-04088: error during execution of trigger 'SCOTT.PU_AR'
Example 3
Using a temporary table
If you need to update a mutating table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers - an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
In the next example, "from the real world", we want to show this. The table CUG can only have records of the following types
A: Type = 1
B: Type = 2 (Leader for C or D)
C: Type = 3 (Lead by B)
D: Type = 4 (Lead by B)
Note, that the types C and D must be leaded by the type B.
Create table CUG
drop table CUG cascade constraints;
create table CUG (
id_cug number(12) not null primary key,
id_B number(12) not null,
type number(1),
foreign key (id_B) references CUG (id_cug)
on delete cascade);Next we create a temporary table to avoid the "Mutating Table Problem".
drop table CUGTMP;
create global temporary table CUGTMP (
id_B number(12),
type number(1))
on commit delete rows;The following trigger checks new rows (Inserts) in CUG
create or replace trigger bi_r
before insert on CUG
for each row
declare
l_type CUG.type%type;
begin
if (:new.type in (3,4)) then
select type into l_type from CUG
where id_cug = :new.id_B;
end if;
if (l_type != 2) then
raise_application_error(-20002,
'C and D CUGs must have a leading B');
end if;
end;
/The following Trigger saves the new values for id_B in the temporary table.
create or replace trigger au_r
after update of id_B on CUG
for each row
begin
insert into CUGTMP (id_B,type)
values (:new.id_B,:new.type);
end;
/The following Trigger finally checks, that C and D CUGs belong to a B CUG.
create or replace trigger au_s
after update of id_B on CUG
declare
l_id_B number(12);
l_typeCD number(1);
l_typeB number(1);
cursor cur_cugtmp is
select id_B,type
from CUGTMP;
begin
open cur_cugtmp;
loop
fetch cur_cugtmp into l_id_B,l_typeCD;
exit when cur_cugtmp%notfound;
select type into l_typeB from CUG
where id_cug = l_id_B;
if (l_typeB != 2) then
raise_application_error(-20002,
'C and D CUGs must have a leading B');
end if;
end loop;
close cur_cugtmp;
end;
/Test insert and update
insert into CUG (id_cug,id_B,type)
values (0,0,0);
insert into CUG (id_cug,id_B,type)
values (1,0,2);
insert into CUG (id_cug,id_B,type)
values (2,0,2);
insert into CUG (id_cug,id_B,type)
values (3,1,3);
insert into CUG (id_cug,id_B,type)
values (4,2,3);
insert into CUG (id_cug,id_B,type)
values (5,1,4);
insert into CUG (id_cug,id_B,type)
values (6,2,4);
commit;SQL> select * from CUG;
ID_CUG ID_B TYPE
---------- ---------- ----------
0 0 0
1 0 2
2 0 2
3 1 3
4 2 3
5 1 4
6 2 4Now, we want that that the CUGs 3,4,5,6 changes the leadership to CUG 2
SQL> update CUG set id_B = 2 where id_cug in (3,4,5,6);
4 rows updated.SQL> select * from cug;
ID_CUG ID_B TYPE
---------- ---------- ----------
0 0 0
1 0 2
2 0 2
3 2 3
4 2 3
5 2 4
6 2 4Next we delete the "Leader" with ID_CUG = 2. All childs must be deleted automatically with the DELETE CASCADE.
SQL> select * from cug;
ID_CUG ID_B TYPE
---------- ---------- ----------
0 0 0
1 0 2Everything looks fine - cool isn't it ?