Contents
What is a mutating table?
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.
What is mutating trigger in Oracle?
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it” message. The second one is simple and performed using compound triggers.
How do you prevent mutating trigger errors?
Avoiding Mutating triggers The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it.” message. Don’t use triggers – The best way to avoid the mutating table error is not to use triggers.
Can we commit after ROLLBACK?
1 Answer. No, you can’t undo, rollback or reverse a commit.
Do we need commit after insert?
So yes, by default, if you’re just using INSERT , the records you insert will be committed, and there is no point trying to roll them back. (This is effectively the same as wrapping each statement between BEGIN and COMMIT .)
Is there a way to avoid the mutating table error?
Don’t use triggers – The best way to avoid the mutating table error is not to use triggers. While the object-oriented Oracle provides “methods” that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
What causes a mutating table / trigger error in Oracle?
A simple row level trigger that causes a mutating table error can result in a very complicated statement level trigger to achieve the needed result. Here are some important items to remember about triggers. On insert triggers have no :OLD values. On delete triggers have no :NEW values. Triggers do not commit transactions.
Can a statement Level trigger be used on a mutating table?
If a trigger does result in a mutating table error, the only real option is to rewrite the trigger as a statement-level trigger. Mutating table errors only impact row level triggers. But to use a statement level trigger, some data may need to be preserved from each row, to be used by the statement level trigger.
What does a mutating table do in PLSQL?
A mutating table occurs when a statement causes a trigger to fire and that trigger references the table that caused the trigger. The best way to avoid such problems is to not use triggers, but I suspect the DBA didn’t take the time to do that. He could have done one of the following: Changed the trigger to an after trigger.