ora-04091 mutating table
来源:互联网 发布:项目流程图软件 编辑:程序博客网 时间:2024/05/17 11:37
ora-04091
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
- ora-04091 mutating table
- ORA-04091: table XXXX is mutating, trigger/function may not see it
- 错误"ORA-04091: table is mutating, trigger/function may not see it"的原因以及解决办法
- ORA-04091: table name is mutating, trigger/function may not see it.
- ORA-04091: table is mutating, trigger/function may not see it
- trigger抛出异常ORA-04091:table ...... is mutating, trigger/function may not see it
- 关于错误:"ORA-04091: table is mutating, trigger/function may not see it"的分析(触发器操作自身表)
- 关于错误:"ORA-04091: table is mutating, trigger/function may not see it"的分析(触发器操作自身表)
- 關於觸發器(Trigger)對於變異表(Mutating table)的操作
- 包含变异表(mutating table)的触发器
- 关于错误:"table is mutating, trigger/function may not see it
- Oracle触发器报错table xxxx is mutating,trigger/function may not see it
- Exteernal table ORA-29913,ORA-30653,KUP-01005
- drop table 时发生ORA-00604 ORA-01422 错
- java.sql.SQLSyntaxErrorException: ORA-01747: user.table.column, table.column
- [datapump] ORA-39034: Table TABLE_DATA:<table Name> does not exist.
- drop table 报错ora- 全分析
- truncate table时发生ORA-02266错误
- 我怎样才能嫁给有钱人?
- CSS控制鼠标形状
- [C]no2.字符串替换
- 特殊目录: /dev/null and /dev/tty
- linux c/c++ GDB教程详解
- ora-04091 mutating table
- pcntl_fork和pcntl_fork 的用法
- SQL 函数
- 在Eclipse中添加插件
- PTP Payload Format for MPEG-4 Elementary Stream(YC)
- 求SAP BW方面的试题
- Android项目中编译 C的模块
- VC中编译、运行程序一些知识
- 通用管理员连接错误