ORA-04091: Table is Mutating

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

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.

14 thoughts on “ORA-04091: Table is Mutating

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

  2. about the PRAGMA AUTONOMOUS_TRANSACTION, i am confused about how to change this case to the

    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
    INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
    — Only commits the preceding INSERT, not the INSERT that fired
    — the trigger.
    — If someone else was hired on the same day, we get an exception
    — because of duplicate values. That’s OK, no action needed.

    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

  3. 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 !! ?

  4. 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?


  5. 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!!!!

  6. 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?

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

  8. 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
    if (N=1) THEN
    end if;

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