Mutating Table:
A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement,or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
A table is not considered mutating for statement triggers.
A mutating table cannot be changed because the resulting DML could change data that is in consistent state.
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.
Mutating error:
Mutating error occurs in the following scenario:
when we are updating a table (trigger written on a table for update) and at the same time trying to retrieve data from that table.
it will result into mutating table and it will result into mutating error.
This happens with triggers.
It occurs because the trigger is trying to update a row it is currently using.
The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
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;
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
A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement,or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
A table is not considered mutating for statement triggers.
A mutating table cannot be changed because the resulting DML could change data that is in consistent state.
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.
Mutating error:
Mutating error occurs in the following scenario:
when we are updating a table (trigger written on a table for update) and at the same time trying to retrieve data from that table.
it will result into mutating table and it will result into mutating error.
This happens with triggers.
It occurs because the trigger is trying to update a row it is currently using.
The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
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;
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