Mutating table/trigger error and how to resolve it
Posted by decipherinfosys on June 22, 2009
Most of us who have worked in Oracle have encountered ORA-04091 (table xxx is mutating. Trigger/function might not see it) at some time or the other during the development process. In this blog post, we will cover why this error occurs and how we can resolve it using different methodology.
Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger. So basically we are trying to select records in the trigger from the table that owns the trigger. This creates inconsistency and Oracle throws a mutating error. Let us take a simple scenario in which we have to know total number of invalid objects after any object status is updated to ‘INVALID’. We will see it with an example. First let us create a table and then trigger.
SQL> CREATE TABLE TEST
2 AS SELECT * FROM USER_OBJECTS;
Table created.
CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
v_Count NUMBER;
BEGIN
SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END;
/
Now if we try to change the status of any object to ‘INVALID’, we will run into mutating error as we are trying to update the record and trigger is trying to select total number of records in ‘INVALID’ status from the same table.
SQL> update test
2 set status = 'INVALID'
3 where object_name = 'TEST1';
update test
*
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
Having said that there are different ways we can handle mutating table errors. Let us start taking one by one scenario.
First one is to create statement level trigger instead of row level. If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Let us create a new statement level trigger.
CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
DECLARE
v_Count NUMBER;
BEGIN
SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END;
Now let us fire the same update statement again.
SQL> UPDATE TEST
2 SET status = 'INVALID'
3 WHERE object_name = 'TEST1';
Total Invalid Objects are 6
1 row updated.
When we defined statement level trigger, update went through fine and it displayed the total number of invalid objects.
Why this is a problem when we are using ‘FOR EACH ROW’ clause? As per Oracle documentation, the session, which issues a triggering statement on the table, cannot query the same table so that trigger cannot see inconsistent data. This restriction applies to all the row level triggers and hence we run into mutating table error.
Second way of dealing with the mutating table issue is to declare row level trigger as an autonomous transaction so that it is not in the same scope of the session issuing DML statement. Following is the row level trigger defined as pragma autonomous transaction.
CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_Count NUMBER;
BEGIN
SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END;
Now let is issue the update statement again and observe the results.
SQL> UPDATE TEST
2 SET status = 'INVALID'
3 WHERE object_name = 'TEST1';
Total Invalid Objects are 5
1 row updated.
If you closely look at the output, you will see only 5 objects shown in invalid status while statement level trigger showed 6 objects in invalid status. Let us try to update multiple objects at the same time.
SQL> UPDATE TEST
2 SET status = 'INVALID'
3 WHERE object_name IN ('T1','T2');
Total Invalid Objects are 6
Total Invalid Objects are 6
2 rows updated.
By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct. The latest updates are not getting reflected in our result set as oppose to statement level trigger. So one has to be very careful when using this approach.
In version 11g, Oracle made it much easier with introduction of compound triggers. We have covered compound triggers in a previous blog post. Let us see in this case how a compound trigger can resolve mutating table error. Let’s create a compound trigger first:
CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND
FOR UPDATE
ON TEST
COMPOUND TRIGGER
/* Declaration Section*/
v_count NUMBER;
AFTER EACH ROW IS
BEGIN
dbms_output.put_line(‘Update is done’);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END AFTER STATEMENT;
END TEST_TRIG_COMPOUND;
/
Now let us check how many objects are invalid in the test table.
SQL> select count(*) from test where status = 'INVALID';
COUNT(*)
———-
6
Here is the update statement followed by an output.
SQL> UPDATE TEST
2 SET status = 'INVALID'
3 WHERE object_name = 'T2';
Update is done
Total Invalid Objects are 7
1 row updated.
Here we get correct result without getting mutating table error. This is also one very good advantage of compound triggers. There are other ways also to resolve mutating table error using temporary tables but we have discussed common ones in this blog post.
Resources:
- Tom Kyte’s article: http://asktom.oracle.com/tkyte/Mutate/index.html
- Don Burleson’s article: http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm
- Oracle 11g PL/SQL language reference: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc.htm
Anonymous said
very good article
Anonymous said
autonomous transaction is very helpfull !!
Thank you
Balachandar R said
Very much useful. Thanks a lot
banoj ku swain said
nice article.it helps me a lot.
Anonymous said
very much useful. thanks alot.
Anonymous said
very help full….
PRAMOD POOLATHODI said
Nice article ! get to know good understanding on Mutating errors; thanks 🙂
Anonymous said
actually i had to work on compound vs mutating trigger. This article helped a lot.
Thank u
Anonymous said
simply superb way to learn mutating trigger…
Cruz Encalada said
Please… PLEASE! Don’t do that. This is a very basic example and of course it compiles and works but is such a very bad practice (a trigger defined as autonomous transaction), it’s not me saying this, please read this article from Tom Kyte, I guess you know who he is: http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html
Anonymous said
Very Nice way to Explain the topic. Thanks
Anonymous said
Simply Awesome 🙂
Sreekanth said
By Using Autonomous Transaction and Statement level Trigger..
SSwaroop said
Awesome article.. Many thanks for such a guidance.
Anonymous said
easy to understand for freshers
Oracle said
Clear and neatly explanation! Very helpful!
Anonymous said
Very helpful. Thanks a lot~
shubhra said
Very Nice article. You are doing a great job.. :)…
Anonymous said
THANKS A LOT GOOD ONE
Rajesh T said
Good article about mutating error
Anonymous said
perfect explanation …..
Anonymous said
nice explanation..thanks so much
Hemant said
nice
Anonymous said
very clear article
Madhu said
Very good explanation..:)
Zhassulan said
Thank you very much!) I think you are the best PL-SQL developer
Anonymous said
Superb.. Thanks a lot.. Your post is really easy to understand..
Anonymous said
Excellent Info..Thanks a lot. You done good job. Plz. keep post.
Kundana said
I read many articles about compound trigger,but this was really good article to start with compound triggers
Anonymous said
Any new bee can understand the complexity of triggers. Thanks a lot.
sinndhuri said
thanks this is very useful
Anonymous said
Nice article in plain simple language…clarified my doubts related to the resolutions for a mutating trigger error.
How To Fix Error 2718 Google Toolbar For Windows 7 Errors - Windows Vista, Windows 7 & 8 said
[…] Mutating table/trigger error and how to resolve it … – Jun 22, 2009 · Most of us who have worked in Oracle have encountered ORA-04091 (table xxx is mutating. Trigger/function might not see it) at some time or the other …… […]
mallikarjun said
I have gud clarity about mutating error by this article.thanks
Anonymous said
Very easily understandable explanation.
Thanks a lot
Anonymous said
What about the temporary table method ??
Anonymous said
hey its nice article
Anonymous said
nice article – Thank you 🙂
Abhimaniu said
Hi
I read a few articles where they have mentioned that we can use AFTER trigger to avoid this error.
Could you see below example and tell me why single row insert on “AFTER” trigger is failing? Where as single row insert with BEFORE trigger is working fine.
Thanks!
———
SQL> DROP TABLE T;
Table dropped
SQL> CREATE TABLE T (ID NUMBER);
Table created
SQL> Insert into T values (1);
1 row inserted
SQL> CREATE OR REPLACE TRIGGER TRG_MUTATING_TEST
2 BEFORE INSERT ON T FOR EACH ROW
3 DECLARE
4 V_NUM NUMBER;
5 BEGIN
6 SELECT COUNT(1) INTO V_NUM FROM T;
7 END;
/
Trigger created
SQL> Insert into T values (2); — Single row Insert works fine.
1 row inserted
SQL> Insert into T select ID*10 from T; — Multi Row Insert fails
Insert into T select ID*10 from T
ORA-04091: table TESTSCHEMA.T is mutating, trigger/function may not see it
ORA-06512: at “TESTSCHEMA.TRG_MUTATING_TEST”, line 4
ORA-04088: error during execution of trigger ‘TESTSCHEMA.TRG_MUTATING_TEST’
SQL> Insert into T values (3);
1 row inserted
Using the same AFTER INSERT trigger, both Single and multi row inserts fails
SQL> CREATE OR REPLACE TRIGGER TRG_MUTATING_TEST
2 AFTER INSERT ON T FOR EACH ROW
3 DECLARE
4 V_NUM NUMBER;
5 BEGIN
6 SELECT COUNT(1) INTO V_NUM FROM T;
7 END;
8 /
Trigger created
SQL> Insert into T values (6);
Insert into T values (6)
ORA-04091: table TESTSCHEMA.T is mutating, trigger/function may not see it
ORA-06512: at “TESTSCHEMA.TRG_MUTATING_TEST”, line 4
ORA-04088: error during execution of trigger ‘TESTSCHEMA.TRG_MUTATING_TEST’
SQL> Insert into T select ID*10 from T;
Insert into T select ID*10 from T
ORA-04091: table TESTSCHEMA.T is mutating, trigger/function may not see it
ORA-06512: at “TESTSCHEMA.TRG_MUTATING_TEST”, line 4
ORA-04088: error during execution of trigger ‘TESTSCHEMA.TRG_MUTATING_TEST’
SQL>
How To Avoid Ora 04091 Table Is Mutating | Information said
[…] Mutating table/trigger error and how to resolve it … – Jun 22, 2009 · Most of us who have worked in Oracle have encountered ORA-04091 (table xxx is mutating. Trigger/function might not see it) at some time or the other … […]
Anonymous said
Very useful…. good article
Sudhas Rajan Babu said
Great explanation !! . Thank u !!
Trigger – oracle | anupambl said
[…] https://decipherinfosys.wordpress.com/2009/06/22/mutating-tabletrigger-error-and-how-to-resolve-it/ […]
Anonymous said
Very Good , Thanks
Dhanasekar said
Thanks for the detailed info about the mutating table error.