06/03/2013

Oracle error ORA-04091 table is mutating, trigger/function may not see it

When working with triggers in Oracle, you may encounter the "ORA-04091 table is mutating, trigger/function may not see it" error. This may happen when you have a trigger running on a table for an insert, update or delete operation and you're running a function which accesses a table being modified at the same time as the trigger.

The fact is, you may not perform further operations on a table which is currently being INSERTed into, UPDATEd or DELETEd FROM in a single statement. For example, you have a BEFORE trigger on myTable and try to run this statement:

UPDATE myTable
SET myColumn = myFunction(someValue);

It will result in our error if myFunction works on myTable to produce a result; this other statement however will not raise an error as the function and trigger executions will be kept separated:

myVariable := myFunction(someValue);

UPDATE myTable
SET myColumn = myVariable;

No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.