There are restrictions on the tables and columns that a trigger body may access.
In order to define these restrictions, it is necessary to understand mutating and constraining tables.
A mutating table is table that is currently being modified by a DML statement and the trigger event also DML statement.
A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.
A constraining table is a table that might need to be read from for a referential integrity constraint.
CREATE OR REPLACE TRIGGER mutating_trigger
FOR EACH ROW
SELECT COUNT (*)
WHERE NO = :OLD.NO;
SQL> delete student where no = 1;
delete student where no = 1
ERROR at line 1:
ORA-04091: table SCOTT.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T", line 4
ORA-04088: error during execution of trigger 'SCOTT.T'
HOW TO AVOID MUTATING TABLE ERROR?
- By using autonomous transaction
- By using statement level trigger