oracle ORA-04091 在 xxx 上插入更新之前创建或替换触发器 xxx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1611348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-04091 on Create or Replace Trigger xxx Before Insert of Update on xxx
提问by kenneth
Can someone help me correct the Trigger below? I am assigned this problem but my technical skill is limited.
有人可以帮我纠正下面的触发器吗？我被分配了这个问题，但我的技术能力有限。
My script hit the error below (ORA 4091):
我的脚本遇到以下错误（ORA 4091）：
Processing STANDARD PO Number: 27179
处理标准采购订单编号：27179
......................................
…………………………………………………………………………………………………………………………………………………………………………
Updating PO Status..
更新采购订单状态..
Done Approval Processing.
完成审批处理。
dist id 611294gl amount 10000.88 bill_del_amount 0 l_amount 10000.88
dist id 611294gl 金额 10000.88 bill_del_amount 0 l_amount 10000.88
some exception occured **ORA-04091: table PO.PO_DISTRIBUTIONS_ALL is mutating,
发生了一些异常 **ORA-04091：表 PO.PO_DISTRIBUTIONS_ALL 正在发生变化，
trigger/function may not see it**
触发器/函数可能看不到它**
ORA-06512: at "APPS.XXAET_PO_DELIVER_TO_TRG",
ORA-06512：在“APPS.XXAET_PO_DELIVER_TO_TRG”处，
line 22
第 22 行
ORA-01403: no data found
ORA-01403: 未找到数据
ORA-04088: error during execution of trigger
ORA-04088: 触发器执行期间出错
'APPS.XXAET_PO_DELIVER
'APPS.XXAET_PO_DELIVER
PL/SQL procedure successfully completed.
PL/SQL 过程成功完成。
SQL>
查询>
I manage to find the customized trigger in clients machine, but after researching I am unable to pin point whats wrong with the sql . Please HELP!
我设法在客户端机器中找到了自定义触发器，但经过研究后，我无法确定 sql 有什么问题。请帮忙！
CREATE OR REPLACE TRIGGER apps.xxaet_po_deliver_to_trg
BEFORE INSERT OR UPDATE ON po_distributions_all
FOR EACH ROW
DECLARE
l_emp_name VARCHAR2 (300);
l_sqlcode VARCHAR (30) := SQLCODE;
l_sqlerrm VARCHAR (400) := SUBSTR (SQLERRM, 1, 399);
x_profile_value VARCHAR (10) ;
BEGIN
x_profile_value := fnd_profile.value('ORG_ID');
Select Ship_To_Location_ID
INTO :NEW.Deliver_To_Location_Id
from PO_LINE_LOCATIONS_ALL
WHERE line_location_id = :NEW.line_location_id
AND ORG_ID = x_profile_value
;
EXCEPTION
WHEN OTHERS
THEN
NULL;
UPDATE PO_DISTRIBUTIONS_ALL SET Deliver_To_Location_Id = :NEW.Deliver_To_Location_Id
WHERE line_location_id = :NEW.line_location_id;
END;
/
Thank you so much! Kenneth.
非常感谢！肯尼斯。
回答by APC
The mutating table error means that trigger code may not issue DML statements on its owning table. This is to avoid recursive behaviour. The solution is quite simple. Remove the UPDATE statement.
变异表错误意味着触发器代码可能不会在其拥有的表上发出 DML 语句。这是为了避免递归行为。解决方法很简单。删除 UPDATE 语句。
The intention behind this statement is not quite clear. This is quite common, as the ORA-4091 error usually indicates poor design such as an insufficiently normalised data model. I think this is your situation. Consequently, resolving the problem requires a lot more business knowledge than we have.
这句话背后的意图还不是很清楚。这很常见，因为 ORA-4091 错误通常表明设计不佳，例如规范化数据模型不足。我想这就是你的情况。因此，解决问题需要比我们更多的业务知识。
It also appears that your SELECT statement is failing (the error stack as posted is a bit confused). You are attempting to suppress the failure of that statement with the notorious WHEN OTHERS THEN NULL;construct. This is Teh Suck! Remove it immediately. You needto know that your code is failing, and why. If the population of :NEW.Deliver_To_Location_Id is optional, so you don't mind if the lookup "fails" replace OTHERSwith NO_DATA_FOUND.
您的 SELECT 语句似乎也失败了（发布的错误堆栈有点混乱）。您正试图用臭名昭著的WHEN OTHERS THEN NULL;构造来抑制该语句的失败。这是Teh Suck！立即将其删除。你需要知道你的代码失败了，以及为什么。如果 :NEW.Deliver_To_Location_Id 的数量是可选的，那么您不介意查找“失败”是否替换OTHERS为NO_DATA_FOUND.
回答by David Aldridge
The root cause of this problem is the use of triggers to do anything other than logging or other non-application related tasks. It's pretty widely regarded as very bad practice to use them for data manipulation in this way.
此问题的根本原因是使用触发器执行除日志记录或其他与应用程序无关的任务之外的任何其他操作。以这种方式使用它们进行数据操作被广泛认为是非常糟糕的做法。
Adding to this is the use of the EXCEPTIONS clause to silently trap errors and invoke that update. And using WHEN OTHERS THEN NULL is a tragedy waiting to happen.
除此之外，还使用 EXCEPTIONS 子句以静默方式捕获错误并调用该更新。使用 WHEN OTHERS THEN NULL 是一场等待发生的悲剧。
All terrible PL/SQL programming practices that should never have passed a code review.
所有不应该通过代码的糟糕的 PL/SQL 编程实践。
Refactoring time.
重构时间。
回答by OMG Ponies
The root cause of the ORA-04091 is the EXCEPTION handling - you can't do anything to the table the trigger is attached to. In this case - PO_DISTRIBUTIONS_ALL
ORA-04091 的根本原因是异常处理 - 您不能对触发器附加到的表做任何事情。在这种情况下 -PO_DISTRIBUTIONS_ALL
Reference: ORA-04091 Error
参考：ORA-04091 错误
I suggest you encapsulate the logic in a stored procedure, and call it in the INSERT/UPDATE stored procedures prior to the INSERT/UPDATE statements.
我建议您将逻辑封装在一个存储过程中，并在 INSERT/UPDATE 语句之前在 INSERT/UPDATE 存储过程中调用它。
回答by Dinesh
As mentioned in earlier answers, you are getting mutating error becasue you are updating same table within trigger which is not allowed.
正如前面的答案中提到的，您会收到变异错误，因为您正在更新触发器内不允许的同一个表。
Since you are using "before update and insert" trigger, all you need to do is set :new.deliver_to_location_id with correct value. Since you are already doing that in select statement, to fix your trigger just remove the update statement, as your select statement is already updating the record field.
由于您使用的是“before update and insert”触发器，您需要做的就是设置 :new.deliver_to_location_id 具有正确的值。由于您已经在 select 语句中执行此操作，因此要修复触发器，只需删除 update 语句，因为您的 select 语句已经在更新记录字段。
As an enhancement, you can replace "when others" exception with "when no_data_found" this will help you to throw all other unexpected errors.
作为增强功能，您可以用“when no_data_found”替换“when others”异常，这将帮助您抛出所有其他意外错误。
