Oracle mutating trigger table errors

Fix Oracle mutating trigger table errors

A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has the possibility of changing.

What this means to a trigger is that if the trigger reads a table, it can not change the table that it read from. This does not impact the exclusive use of :OLD and :NEW.

It says that if the trigger reads the table (such as using a SELECT query), that changes (even using :NEW) will fail. This can also happen when a trigger on a parent table causes an insert on a child table referencing a foreign key.

Mutating Tables

Each new release of the Oracle database reduces the impact of the mutating table error on triggers and they are much less of a problem with Oracle9i and above. 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. This data can be stored in a PL/SQL collection or in a temporary table. 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. If a transaction is rolled back, the data changed by the trigger is also rolled back.
• Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none.
• Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger.
• If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order.
• A trigger can cause other events to execute triggers.
• A trigger can not change a table that it has read from. This is the mutating table error issue.

The fact that a trigger can cause other triggers to fire is an important item to remember. A trigger that causes other database events to execute triggers can cause the database crash. For example, the database can capture server errors by defining a trigger on the database server error event.

But if this trigger causes a server error, the database will spin in a loop, with each firing of the trigger causing the error, firing the trigger again, and again, and again. The only way to regain control of the database is to disable the trigger.

Avoiding Mutating triggers

The insert to the child table caused the foreign key to validate the data on the parent (which fired the trigger) causing the insert of the child table to result in a mutating table error on the parent table.

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.  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" or "instead of" 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 - Dr. Hall has some great notes on mutating table errors, and offers 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.

At the end of the day, the mutating table error is usually the result of a poor application design and mutating triggers should be avoided whenever possible.

Steve Callan has these notes on the ORA-04091 error:

"Here is a problem many developers run into: ORA-04091 table owner.table_name is mutating, trigger/function may not see it. In many cases, the cause of this error is due to code within a trigger that looks at or touches the data within the table the trigger is being called or invoked from.
The reason Oracle raises this error is related to one of Oracle's primary strengths as a relational database management system. The particular strength in question here is that of having a read consistent view of data.

It is worthwhile to note that this ORA-04091 error occurs not only in the "pure" database development environment, but also in the Oracle tools type of development environment such as Oracle SQL*Forms.
Another solution relies on using an INSTEAD-OF trigger instead of the trigger you meant to use when you received the error. Another solution is actually more of a preventative measure, namely, using the right type of trigger for the task at hand.
Perhaps the greatest strength or utility of an INSTEAD-OF trigger is its ability to update what would normally appear to be non-updateable views. Simple views (pretty much based on a single base table) generally are inherently updateable via DML statements issued against the view.
However, when a view becomes more complex (multiple tables or views used in various join conditions to create the new single view), there is a good chance that many columns, as referenced by the view, lose their "updateable-ness."
So, being the data dictionary view/table name trivia wizard that you are, you know to query the XXX_UPDATABLE_COLUMNS views, substituting USER, ALL or DBA for XXX as applicable.

There are exceptions to this rule about views being inherently updateable. The exceptions (or restrictions) include views that use aggregate functions; group functions; use of the DISTINCT keyword; use of GROUP BY, CONNECT BY or START WITH clauses; and use of some joins. In many cases, use of the INSTEAD-OF trigger feature allows you to work around these restrictions.

INSTEAD-OF triggers are also useful for Forms developers because forms are commonly based on views. The INSTEAD-OF trigger, being a "real" trigger, and not a true form trigger, is stored on the server."