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