|
Handling mutating table trigger problem:
This article explain a scenario of mutating table error on trigger.
Suppose u had written a "AFTER DELETE" trigger on "EMP" table as below:
CREATE TABLE test
(t_empno NUMBER(5));
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON emp
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
INSERT INTO test VALUES(:OLD.empno);
SELECT COUNT(*) INTO n
FROM emp;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||' employees.');
END;
/
commit; |
Then, after firing "DELETE" statement on "EMP" table, u will get errors like:
SQL> delete from emp where empno=7788;
delete from emp where empno=7788
*
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.EMP_COUNT", line 7
ORA-04088: error during execution of trigger 'SCOTT.EMP_COUNT'
Because, u r firing "SELECT", statement on the "EMP" table itself, on which
trigger was written;
So, to avoid this u can handle the mutating table case in exception as below:
CREATE TABLE test
(t_empno NUMBER(5));
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON emp
FOR EACH ROW
DECLARE
n INTEGER;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT(mutating_table,-4091);
BEGIN
INSERT INTO test VALUES(:OLD.empno);
SELECT COUNT(*) INTO n FROM emp;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||' employees.');
EXCEPTION
WHEN mutating_table THEN
null;
END;
/
commit;
|
Now , if u fire the delete statement on "EMP" table, then the trigger will
get executed as per requirement. There will be no errror of muating_tables.
|
