Many a times you must have encountered the issue ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it.
This issue basically persists in PLSQL triggers. At one time I was also hit by the same issue. One of my friend “Nikhil Tungare – PLSQL Guru” helped me to get rid of this issue.
Here is how the error can be reproducible.
SQL> create table test1
2 (col1 varchar2(10),
3 col2 number);
Table created.
SQL> create table test1_audit
2 (col1 number,
3 time date);
Table created.
SQL> create or replace trigger mutat_trig
2 after insert on test1
3 for each row
4 declare
5 id number;
6 begin
7 select col2 into id from test1
8 where col2 = :NEW.col2;
9 insert into test1_audit values (id, sysdate);
10 end;
11 /
Trigger created.
SQL> insert into test1 values(‘test’,1);
insert into test1 values(‘test’,1)
*
ERROR at line 1:
ORA-04091: table SYSTEM.TEST1 is mutating, trigger/function may not see it
ORA-06512: at “SYSTEM.MUTAT_TRIG”, line 4
ORA-04088: error during execution of trigger ‘SYSTEM.MUTAT_TRIG’
The reason for this error is because, you have a table and you are inserting a row. Now as soon as you insert a row, a trigger is fired which will select the inserted data. This causes a problem because the data is inconsistent, it is not yet commited. Oracle engine allows only commited data to be queried.
This problem comes with row level trigger only, because row level trigger will gets fired immidiately as soon as you insert a row in a table. Statement level trigger will get fired after every statement.
To get rid of this problem and still use row level trigger, we have the solution as given below.
We need to create following triggers to avoide this issue.
1. After RowLevel
2. After Statement Level
Also we need to declare a global variable to store the value of ID that we will select. This global variable should be declared in package, so that we can access it when ever required.
Create a package, which will hold global variable
SQL> CREATE OR REPLACE PACKAGE test_package AS
2 id test1.col2%TYPE;
3 END;
4 /
Package created.
1) Creating After Row level trigger to populate the global variable with required value
SQL> create or replace trigger mutat_trig
2 after insert on test1
3 for each row
4 begin
5 test_package.id := :new.col2;
6 end;
7 /
Trigger created.
2) Create a After Statement level trigger to insert into test1_audit table.
SQL> create or replace trigger insert_audit
2 after insert on test1
3 begin
4 insert into test1_audit values (test_package.id, sysdate);
5 end;
6 /
Trigger created.
Trying to insert value now.
SQL> insert into test1 values(‘test’,1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1_audit;
COL1 TIME
———- ———
1 30-AUG-07
So always avoid selecting the value from the table, which is not yet committed. Thanks for Nikhil for helping me figure out this very closely.
Persistent Storage Solutions 
Actually , there is another way – Oracle 9i release 2 onwards
use a PRAGMA AUTONOMOUS_TRANSACTION before the query in the
mutating trigger
then you can query the same table –
But if you try to insert / update the same table , it will fail
Instead of creating two / three more triggers , use an AUTONOMOUS TRANSACTION
, plus point here is that you can COMMIT inside the trigger as well
how to use using AUTONOMOUS TRANSACTION. can you explain with some example please.
thanks a lot
Thank you a lot, both blogger and commenter 😀
about the PRAGMA AUTONOMOUS_TRANSACTION, i am confused about how to change this case to the
autonomous
i found an example from Oracle Document
The following example lets a trigger issue transaction control statements:
CREATE TABLE anniversaries AS
SELECT DISTINCT TRUNC(hire_date) anniversary FROM employees;
ALTER TABLE anniversaries ADD PRIMARY KEY (anniversary);
CREATE TRIGGER anniversary_trigger
BEFORE INSERT ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
— Only commits the preceding INSERT, not the INSERT that fired
— the trigger.
COMMIT;
EXCEPTION
— If someone else was hired on the same day, we get an exception
— because of duplicate values. That’s OK, no action needed.
WHEN OTHERS THEN NULL;
END;
but how to do insering table as independtent behavior and commit first, then use the new inserted data to insert to the other table, as this article said.
by the way, is there any other solution for this case??
is anybody heared “temporary table” and maybe it is other solution
St_Bal|Clearing|Funding|Adj_Credit|Adj_Debit|Cl_Bal|Date |
————————————————————–
10.00 | 0.00 | 0.00 | 0.00 | 0.00 | 10.00| 01/01/08 |
—————————————————————
10.00 | 0.00 |100.00 | 0.00 | 0.00 |110.00| 01/02/08 |
—————————————————————
110.00| 0.00 | 0.00 | 10.00 | 0.00 | 100.00|01/03/08|
—————————————————————
Now If someone goes to record of 01/01/08 and update the value of starting balance to 20.00 from 10.00
i need to write trigger to do update all next onward data .that but it gives me error
ORA-04091: table is mutating, trigger/function may not see it.
and if i do the way discuss above it gives error that update exceed the limit
any idea how to figure it out !! ?
Hi,
I need to write an AFTER INSERT triiger on table1 FOR EACH ROW and then need to uodate the table1 itself inside the trigger.
Can you please guide me how will it be possible?
Thanks
That doesn’t make sense to me. The mutating table error is caused by selecting from the table that is mutating. Your solution is a convoluted way of just inserting the value :new.col2. Why do you need to use a package and a statement level trigger to get that value? Your solution has totally removed the select altogether so why not just do that in the code you used to highlight the original problem
I have a problem with a mutating table error. I have to select from the mutating table and an autonomous transaction wont work because I cant select from the mutating table during an autonomous transaction as the record won’t have been committed by this point.
I am stumped!!!!
I do apologise. It works. Problem solved. Thank you very much!
Paul.
PRAGMA AUTONOMOUS_TRANSACTION;
is superb man…. thanks
is there any side effect for it?
Good posting solution of ORA-04091 error.
Great post and comments! However, I have a question:
Is the original post (with the package variable) thread-save? Would two parallel inserts mess each other up as both are setting the package variable? Or are the variables bound to a session?
Yeah,
I have a function that returns a kind of flag to tell me if updated record is of particular type. Couldn’t write normal where statement as this is dynamic query that doesn’t have enough information. Obviously, despite the fact that in function I don’t use any of updated columns I was getting ORA-04091. Autonomous transaction helped! Thanks!
CREATE table ABC
( abc_id number(10) primary key,
abc_name varchar2(30)
);
create table XYZ
( xyz_id number(10) not null,
xyz_name varchar2(30) ,
foreign key(xyz_id) references ABC(abc_id) on delete cascade
);
I want to create a trigger such that in table XYZ the last reccord is deleted then corresponding reccord in table ABC should be deleted but i keep getting mutilating error can ny one help me out .
Create or replace trigger backup after
delete on XYZ for each row
DECLARE
N INTEGER;
begin
commit;
SELECT COUNT(*) INTO N FROM XYZ WHERE XYZ_id=:old.xyz_id;
if (N=1) THEN
DELETE FROM ABC WHERE ABC_ID=:old.xyz_id;
end if;
end;