Handle trigger Mutating Error

April 25, 2017Posted inORACLE

Many times we have faced the problem of trigger Mutating error.

error ORA-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 test_mutating 
  ( 
     id   NUMBER, 
     name VARCHAR2(400 byte) 
  );
  1. Create trigger
Copy Code
CREATE 
OR 
replace TRIGGER t1 afterINSERT 
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); 
ENDLOOP;
EXCEPTION 
WHEN others THEN 
  dbms_output.put_line ('error' || sqlerrm);
  END;

  1. Insert value
INSERT INTO test_mutating 
VALUES      (1, 'ashish'); 

error ORA-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;

  1. Recompile the trigger with using “PRAGMA AUTONOMOUS_TRANSACTION”
Copy Code
CREATE OR replace TRIGGER t1 after INSERT 
on test_mutating for each row
declare 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;

  1. Now insert reocord
INSERT INTO test_mutating 
VALUES      (1, 'ashish');

1 row inserted.

Tags: