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'.
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(*)
----------
0
It 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 <===== OK
SQL> 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 row
declare
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) then
open 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 4
Now, 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 4
Next 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 2
Everything looks fine - cool isn't it ?
|
