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
- Create Table
CREATE TABLE test_mutating
(
id NUMBER,
name VARCHAR2(400 byte)
);
- Create trigger
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;
- 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;
- Recompile the trigger with using “PRAGMA AUTONOMOUS_TRANSACTION”
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;
- Now insert reocord
INSERT INTO test_mutating
VALUES (1, 'ashish');
1 row inserted.