The
Mutating table error is a well-known problem encountered by most developers has
come across this error.
ORA-04091: table
<tablename> is mutating,
trigger/ function may not see it
Oracle
has a very unique feature of read
consistent view of data & when developers trying to break this rule Mutation
errors come and stop the developer to break the rule So Mutation error is
a friend of developers.
Lets Understand Mutation in detail.
The
basic reason for this error is the way Oracle manages a read consistent view of
data at a given point of time.You
can only see consistent data. When we try to break this read consistency we
face Mutation which simply says that you are breaking the rule and stop you do so and save your data & Object structure from corruption.
Objects
with which you will face this error are
- Triggers
- Functions
Most
of the developers just think it will happen with triggers only but that’s not
the case.
When & in which Situation
Mutation error occurs with Triggers.
Case
1: When Trigger on table refers the same table:
Note: Mutating error with
Triggers Occurs only for row level triggers.
not for statement level triggers
Operation
|
Type
|
Mutating
|
Insert
|
Before Row Level
|
Only for Multi Row insert
|
Insert
|
After Row Level
|
Yes for both
|
Update
|
Before Row Level
|
Yes for both
|
Update
|
After Row Level
|
Yes for both
|
Delete
|
Before Row Level
|
Yes for both
|
Delete
|
After Row Level
|
Yes for both
|
The error is encountered when a row-level trigger accesses the same table
on which it is based, while executing. The table is said to be mutating.
Mutation will not occur if a single record is inserted in the table (using
VALUES clause). If bulk insertion is done or data is inserted from another
table mutation will occur.
The
mutating error is not only encountered during queries, but also for insert,
updates and deletes present in the trigger.
Key points for before row level Insert Triggers
- Only for multiple rows,
with sub query syntax even in sub query we select only one value.
- After row level triggers generate
mutation errors for single and multiple rows
See examples
SQL>
create table mtt_same_table (col1 number,col2 varchar2(10));
Table created.
SQL> create or replace trigger
mutation_on_same_table
before insert or update or delete on
mtt_same_table
for each row
declare
vcount number;
begin
select count(*) into vcount from mtt_same_table;
dbms_output.put_line('mtt');
end;
/
Trigger created.
SQL> select * from mtt_same_table;
no rows selected
-- we can insert single row data with before
row level triggers
SQL> insert into mtt_same_table
values(1,'arun');
1 row created.
--when we try to insert multiple rows using sub query
-- Mutation error prevent you to do do even sub query returns no row.
SQL> insert into mtt_same_table
select salary , last_name from employees where employee_id=101
insert into mtt_same_table select salary
, last_name from employees where employee_id=101
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is
mutating, trigger/function may not see
it
ORA-06512: at
"SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.MUTATION_ON_SAME_TABLE'
-- When we try to update
mutation error prevents to do so
SQL> update mtt_same_table set col1=null
;
update mtt_same_table set col1=null
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is
mutating, trigger/function may not see
it
ORA-06512: at
"SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.MUTATION_ON_SAME_TABLE'
-- When we select from sub query ,either
its one value or many mutation error you face because oracle don’t know how
many rows are there until it fetch the rows
QL> insert into mtt_same_table select 1,'arun' from dual
SQL> /
insert into mtt_same_table select
1,'arun' from dual
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is
mutating, trigger/function may not see
it
ORA-06512: at
"SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.MUTATION_ON_SAME_TABLE'
--
DELETE example
SQL> delete from mtt_same_table;
delete from mtt_same_table
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is
mutating, trigger/function may not see
it
ORA-06512: at
"SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.MUTATION_ON_SAME_TABLE'
-- Now the After row level triggers and Insert , You face Mutation with single row insertion as well unlike before row level triggers
SQL> create or replace trigger mutation_on_same_table
After insert or update or
delete on mtt_same_table
for each row
declare
vcount number;
begin
select count(*) into vcount from mtt_same_table;
dbms_output.put_line('mtt');
end;
/
Trigger
created
1* insert into mtt_same_table values (2,'mass')
SQL> /
insert into mtt_same_table values (2,'mass')
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is
mutating, trigger/function may not see it
ORA-06512: at
"SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.MUTATION_ON_SAME_TABLE'
Case
2 when Trigger on table refers the different table
This
happens when a foreign key reference
is present with an on-delete-cascade option.
A row level trigger on the master table will mutate if the detail table is
being referred to in the trigger, for a delete transaction. This will only
happen if the foreign key on the detail table is created with the on delete cascade option.
No
mutation occurs if the master table is being referred in a trigger on the
detail table.
-- master table
SQL> create table mtt_master_table
(id number primary key ,name varchar2(10));
Table created.
--
Child table with foreign key and on delete cascade constraint.
SQL> create table mtt_child_table
(id1 number references mtt_master_table(id) on delete cascade , name
varchar2(10));
Table created.
--
Trigger on Master table
SQL>
create or replace trigger trig_diff_table
before insert or delete or update on
mtt_master_table
for each row
declare
vcount number;
begin
select count(*) into vcount from mtt_child_table ;
end;
SQL> /
Trigger created.
--
insert data in master table
SQL> insert into mtt_master_table
values(1,'arun');
1 row created.
SQL> insert into mtt_master_table
values(2,'varun');
1 row created.
--
insert into child table
SQL> insert into mtt_child_table
values(1,'bharat');
1 row created.
SQL>
insert into mtt_child_table values(1,'mbharat');
1 row created.
--
update master table as there are on delete cascade constarint is present which tries to delete
the Corresponding record from the child table, and in the trigger we are selecting
data from the Child table and data is changing.So mutation prevent to do so.
SQL> select * from mtt_master_table;
ID NAME
---------- ----------
1 arun
2 varun
SQL> select * from mtt_child_table;
ID1 NAME
---------- ----------
1 bharat
1 mbharat
SQL> delete from mtt_master_table
where id=2;
delete from mtt_master_table where id=2
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_CHILD_TABLE
is mutating, trigger/function may not see it
ORA-06512: at
"SCOTT.TRIG_DIFF_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.TRIG_DIFF_TABLE'
-- BUT WE CAN DELETE THE DATA FROM THE
CHILD TABLE
SQL> delete from mtt_child_table;
2 rows deleted.
SQL> select count(*) from
mtt_child_table;
Count(*)
-----------------
0
--Even there is no row in the child still we cannot delete from master table
SQL> delete from mtt_master_table
2 /
delete from mtt_master_table
*
ERROR at line 1:
ORA-04091: table SCOTT.MTT_CHILD_TABLE
is mutating, trigger/function may not see it
ORA-06512: at
"SCOTT.TRIG_DIFF_TABLE", line 4
ORA-04088: error during execution of
trigger 'SCOTT.TRIG_DIFF_TABLE'
--we just simply disable the trigger
and see the results
SQL> alter trigger trig_diff_table
disable;
Trigger altered.
SQL> delete from mtt_master_table ;
2 rows deleted.
We can delete now.
Case 3 .Mutation error with
Functions:
Mutation can occurs with functions as well,
many of us think Mutation happens with Triggers only but it’s not true.In a
function we are counting the no of rows from table and returning the count.
We are trying to insert into same table
using this function.. Which will give us mutation error?
-- CREATE TABLE
SQL> create table mtt_function_table (id
number);
Table
created.
-- CREATE FUNCTION WHICH REFER THE
MTT_FUNCTION_TABLE
SQL
> create or replace function mtt_function return number
is
vcount
number;
begin
select
count(*) into vcount from mtt_function_table;
return vcount;
end;
/
Function created
-- Now we are trying to use this functions output in a insert statement as a value and at the same time we are refering the same table so Mutating
function ORA-04091
SQL>
insert into mtt_function_table select mtt_function() from
mtt_function_table;
insert
into mtt_function_table select mtt_function() from mtt_function_table
*
ERROR
at line 1:
ORA-04091:
table SCOTT.MTT_FUNCTION_TABLE is mutating, trigger/function may not see it
ORA-06512:
at "SCOTT.MTT_FUNCTION", line 5
SQL> 1* insert into mtt_function_table select
mtt_function() from dual;
insert
into mtt_function_table select mtt_function() from dual
*
ERROR
at line 1:
ORA-04091:
table SCOTT.MTT_FUNCTION_TABLE is mutating, trigger/function may not see it
ORA-06512:
at "SCOTT.MTT_FUNCTION", line 5
Now the
Question is how to avoid this
- Don’t break the rule , don’t use the same table
for insertion and selection at the same time , use temporary table or
views.
- Don’t use triggers, modern developers don’t use
trigger
- If you have requirement that you have to break
the rule then there is one more friend of developers Autonomous
Transaction
- Use
compound triggers to avoid mutation Compound triggers
Let
us use the autonomous transaction approach for all the above examples we
describe.
Case
1: When Trigger on table refers the same table:
We
just use the Autonomous transaction Pragma to break the rule here
-- create
trigger with Pragma autonomous transaction & see the magic
create
or replace trigger mutation_on_same_table
before insert or update or delete on
mtt_same_table
for each row
declare
vcount number;
pragma Autonomous_transaction;
begin
select count(*) into vcount from
mtt_same_table;
dbms_output.put_line('mtt');
end;
/
--Now as we create the trigger
with Pragma autonomous transaction we are ready to break the rule we can insert multiple value using sub query
for a before row level trigger , update and delete from the same table
--single row Insert example
SQL> insert into mtt_same_table values (1,'ar');
1 row created.
--multiple rows with sub query example
SQL> insert into mtt_same_table select employee_id
,substr(last_name,1,6) from employees;
109 rows created.
SQL> select count(*) from mtt_same_table;
COUNT(*)
----------
110
--update table example
SQL> update mtt_same_table set col1=null ;
110 rows updated.
--delete from table example
SQL> delete from mtt_same_table;
110 rows deleted.
SQL> select count(*) from mtt_same_table ;
COUNT(*)
----------
0
Case
2 when Trigger on table refers the different table
Now we user Pragma autonomous transaction
to break this rule with on delete cascade constraint, we will delete the records
with trigger referring to the child table
SQL> create or replace trigger trig_diff_table
before
insert or delete or update on
mtt_master_table
for each
row
declare
vcount
number;
pragma autonomous_transaction;
begin
select
count(*) into vcount from mtt_child_table ;
end;
/
Trigger created.
--see the status of the trigger is enabled
SQL> select TRIGGER_NAME,STATUS from user_triggers
where trigger_name='TRIG_DIFF_TABLE';
TRIGGER_NAME STATUS
------------------------------ --------
TRIG_DIFF_TABLE ENABLED
--check no of records in master table
SQL> SELECT COUNT(*) FROM MTT_MASTER_TABLE;
COUNT(*)
----------
2
--check no of records in child table
SQL> SELECT COUNT(*) FROM MTT_CHILD_TABLE;
COUNT(*)
----------
1
--delete from master table and see the magic of pragma Autonomous Transaction
SQL> DELETE FROM MTT_MASTER_TABLE;
2 rows deleted.
SQL> SELECT COUNT(*) FROM MTT_MASTER_TABLE;
COUNT(*)
----------
0
--see child records also deleted as we have the on
delete cascade and We are referring the same table in trigger
SQL> SELECT COUNT(*) FROM MTT_CHILD_TABLE;
COUNT(*)
----------
0
SQL> select TRIGGER_NAME,STATUS from user_triggers
where trigger_name='TRIG_DIFF_TABLE';
TRIGGER_NAME STATUS
------------------------------ --------
TRIG_DIFF_TABLE ENABLED
Case 3
.Mutation error with Functions:
Now we insert the function output in the same table , and function is referring the same table
--create function with PRAGMA AUTONOMOUS_TRANSACTION
SQL> create
or replace function mtt_function return number
is
vcount
number;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select
count(*) into vcount from mtt_function_table;
return vcount;
end;
/
Function created.
--now we can insert into the same table function output and while in a function we are refering the same table
SQL> insert
into mtt_function_table select mtt_function() from mtt_function_table;
1 row created.
SQL> insert into mtt_function_table select
mtt_function() from dual;
1
row created.
As we all see
the magic of Autonomous Transaction Pragma , but It does not mean every time you
just use the Pragma to break the rules
See detail uses
of Pragama Autonomous Transaction in my next blog.
Your comments suggestions are awaited :0
