ora-04091 mutating table

 

ora-04091

 

分类: Oracle Problems

2008.12.20 15:32 作者：oraclespace | 评论：0 | 阅读：80

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" 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."

 

 

Cause
A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action
Rewrite the trigger (or function) so it does not read that table.

 

你可以通过这个链接引用该篇文章:http://oraclespace.bokee.com/viewdiary.41206984.html