October 13, 2008

Mutating Tables

OK, so you've just recieved the error:
ORA-04091: table XXXX is mutating, trigger/function may not see it

What is mutating table?
A mutating table is a table that is currently being modified by an update, delete, or insert statement. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key
columns of the table the trigger is triggering off of.
Mutatation of table is a state of table when in before /after update trigger table tries to update the same table.As the call of update also calls same trigger then the trigger will be called infinite times. then the table will be mutating .

When mutating table error occurs?
I have a table containing a key/status/effective date combination. When status changes, the values are propagated by trigger to a log table recording the status history. When no RI constraint is in place everything works fine.
When an RI trigger enforces a parent-child relationship, the status change logging trigger fails because the parent table is mutating. Propagating the values to the log table implicitly generates a lookup back to the parent table to ensure the RI constraint is satisfied.

Fix Oracle mutating trigger table errors:
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.
Use an "after" trigger - If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
Re-work the trigger syntax – Find other ways to avoid mutating tables with a combination of row-level and statement-level triggers.
Use autonomous transactions - You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.

No comments: