MUTATING TABLES
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 currentlty 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.
Ex:
CREATE OR REPLACE TRIGGER
MUTATING_TRIGGER
before delete
on student
for each row
DECLARE
ct number;
BEGIN
select count(*) into ct from student where no = :old.no;
END MUTATING_TRIGGER;
Output:
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
No comments:
Post a Comment