Systems Engineering and RDBMS

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:

40 Responses to “Mutating table/trigger error and how to resolve it”

  1. Anonymous said

    very good article

  2. Anonymous said

    autonomous transaction is very helpfull !!

    Thank you

  3. Balachandar R said

    Very much useful. Thanks a lot

  4. banoj ku swain said

    nice article.it helps me a lot.

  5. Anonymous said

    very much useful. thanks alot.

  6. Anonymous said

    very help full….

  7. PRAMOD POOLATHODI said

    Nice article ! get to know good understanding on Mutating errors; thanks🙂

  8. Anonymous said

    actually i had to work on compound vs mutating trigger. This article helped a lot.

    Thank u

  9. Anonymous said

    simply superb way to learn mutating trigger…

  10. 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

  11. Anonymous said

    Very Nice way to Explain the topic. Thanks

  12. Anonymous said

    Simply Awesome🙂

  13. Sreekanth said

    By Using Autonomous Transaction and Statement level Trigger..

  14. SSwaroop said

    Awesome article.. Many thanks for such a guidance.

  15. Anonymous said

    easy to understand for freshers

  16. Oracle said

    Clear and neatly explanation! Very helpful!

  17. Anonymous said

    Very helpful. Thanks a lot~

  18. shubhra said

    Very Nice article. You are doing a great job.. :)…

  19. Anonymous said

    THANKS A LOT GOOD ONE

  20. Rajesh T said

    Good article about mutating error

  21. Anonymous said

    perfect explanation …..

  22. Anonymous said

    nice explanation..thanks so much

  23. Hemant said

    nice

  24. Anonymous said

    very clear article

  25. Madhu said

    Very good explanation..:)

  26. Zhassulan said

    Thank you very much!) I think you are the best PL-SQL developer

  27. Anonymous said

    Superb.. Thanks a lot.. Your post is really easy to understand..

  28. Anonymous said

    Excellent Info..Thanks a lot. You done good job. Plz. keep post.

  29. Kundana said

    I read many articles about compound trigger,but this was really good article to start with compound triggers

  30. Anonymous said

    Any new bee can understand the complexity of triggers. Thanks a lot.

  31. sinndhuri said

    thanks this is very useful

  32. Anonymous said

    Nice article in plain simple language…clarified my doubts related to the resolutions for a mutating trigger error.

  33. […] 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 …… […]

  34. mallikarjun said

    I have gud clarity about mutating error by this article.thanks

  35. Anonymous said

    Very easily understandable explanation.
    Thanks a lot

  36. Anonymous said

    What about the temporary table method ??

  37. Anonymous said

    hey its nice article

  38. Anonymous said

    nice article – Thank you🙂

  39. 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>

  40. […] 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 … […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: