ORA-04091：在 xxx 上插入更新之前创建或替换触发器 xxx
有人可以帮我纠正下面的触发器吗？我被分配了这个问题，但我的技术能力有限。
我的脚本遇到以下错误 (ORA 4091)：
处理标准采购订单编号：27179
................................ ............
正在更新采购订单状态..
已完成审批处理。
dist id 611294gl amount 10000.88 bill_del_amount 0 l_amount 10000.88
发生一些异常 **ORA-04091：表 PO.PO_DISTRIBUTIONS_ALL 正在发生变化，
触发器/函数可能看不到它**
ORA-06512：位于“APPS.XXAET_PO_DELIVER_TO_TRG”，
第 22 行
03 ：未找到数据
ORA-04088：执行触发器
“APPS.XXAET_PO_DELIVER
PL/SQL 过程成功完成”期间出错。
SQL>
我设法在客户端计算机中找到自定义触发器，但经过研究后我无法查明 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;
/
非常感谢！ 肯尼思.
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):
Processing STANDARD PO Number: 27179
......................................
Updating PO Status..
Done Approval Processing.
dist id 611294gl amount 10000.88 bill_del_amount 0 l_amount 10000.88
some exception occured **ORA-04091: table PO.PO_DISTRIBUTIONS_ALL is mutating,
trigger/function may not see it**
ORA-06512: at "APPS.XXAET_PO_DELIVER_TO_TRG",
line 22
ORA-01403: no data found
ORA-04088: error during execution of trigger
'APPS.XXAET_PO_DELIVER
PL/SQL procedure successfully completed.
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!
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
变异表错误意味着触发器代码可能无法在其所属表上发出 DML 语句。这是为了避免递归行为。解决方案非常简单。删除 UPDATE 语句。
此声明背后的意图尚不清楚。这种情况很常见，因为 ORA-4091 错误通常表明设计不佳，例如数据模型标准化不足。我想这就是你的情况。因此，解决问题需要比我们更多的业务知识。
您的 SELECT 语句似乎也失败了（发布的错误堆栈有点混乱）。您试图使用臭名昭著的
WHEN OTHERS THEN NULL;结构来抑制该语句的失败。这是 Teh Suck！立即将其删除。您需要知道您的代码失败了以及原因。如果 :NEW.Deliver_To_Location_Id 的填充是可选的，那么您不介意查找“失败”，请将OTHERS替换为NO_DATA_FOUND。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.
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.
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 need to 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" replaceOTHERSwithNO_DATA_FOUND.此问题的根本原因是使用触发器执行日志记录或其他非应用程序相关任务以外的任何操作。人们普遍认为以这种方式使用它们进行数据操作是非常糟糕的做法。
除此之外，还可以使用 EXCEPTIONS 子句来静默地捕获错误并调用该更新。使用 WHEN OTHERS THEN NULL 是一场即将发生的悲剧。
所有糟糕的 PL/SQL 编程实践都不应该通过代码审查。
重构时间。
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.
All terrible PL/SQL programming practices that should never have passed a code review.
Refactoring time.
ORA-04091 的根本原因是异常处理 - 您无法对触发器所附加的表执行任何操作。在本例中 -
PO_DISTRIBUTIONS_ALL参考：ORA-04091 错误
我建议您将逻辑封装在存储过程中，并在 INSERT/UPDATE 语句之前在 INSERT/UPDATE 存储过程中调用它。
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_ALLReference: ORA-04091 Error
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.
正如前面的答案中提到的，您会收到变异错误，因为您正在触发器内更新同一个表，这是不允许的。
由于您使用的是“更新和插入之前”触发器，因此您所需要做的就是使用正确的值设置 :new.deliver_to_location_id 。由于您已经在 select 语句中执行此操作，因此要修复触发器，只需删除更新语句即可，因为您的 select 语句已经在更新记录字段。
作为增强功能，您可以将“when others”异常替换为“when no_data_found”，这将帮助您抛出所有其他意外错误。
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.
As an enhancement, you can replace "when others" exception with "when no_data_found" this will help you to throw all other unexpected errors.