Handle trigger Mutating Error

Hey Guys,

Many times we have faced the problem of trigger Mutating error

errorORA-04091: table TEST1.TEST_MUTATING is mutating, trigger/function may not see it

 let see how it comes and resolve

1. Create Table

CREATE TABLE TEST1.TEST_MUTATING(  ID    NUMBER,  NAME  VARCHAR2(400 BYTE));

2. Create trigger

CREATE OR REPLACE TRIGGER t1   AFTER INSERT   ON test_mutating   FOR EACH ROWDECLARE   CURSOR c1   IS      SELECT ID, name FROM test_mutating;
   l_id     test_mutating.id%TYPE;   l_name   TEST_MUTATING.NAME%TYPE;BEGIN   OPEN c1;
   LOOP      FETCH c1      INTO l_id, l_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.put_line ('ID := ' || l_id || 'l_NAME : = ' || l_name);   END LOOP;EXCEPTION   WHEN OTHERS   THEN      DBMS_OUTPUT.put_line ('error' || SQLERRM);END;

3. Insert value

INSERT INTO test_mutating     VALUES (1, 'ashish');

errorORA-04091: table TEST1.TEST_MUTATING is mutating, trigger/function may not see it


We cannot select value from table at the same event of insertion.

To resolve this problem we use

PRAGMA AUTONOMOUS_TRANSACTION;

4.  Recompile the trigger with using "PRAGMA AUTONOMOUS_TRANSACTION"

CREATE OR REPLACE TRIGGER t1   AFTER INSERT   ON test_mutating   FOR EACH ROWDECLARE   CURSOR c1   IS      SELECT ID, name FROM test_mutating;
   l_id     test_mutating.id%TYPE;   l_name   TEST_MUTATING.NAME%TYPE;   PRAGMA AUTONOMOUS_TRANSACTION;BEGIN   OPEN c1;
   LOOP      FETCH c1      INTO l_id, l_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.put_line ('ID := ' || l_id || 'l_NAME : = ' || l_name);   END LOOP;EXCEPTION   WHEN OTHERS   THEN      DBMS_OUTPUT.put_line ('error' || SQLERRM);END;

5. Now insert reocord

INSERT INTO test_mutating
     VALUES (1, 'ashish');


1 row inserted.