Oracle触发错误ORA-04091 [英] Oracle trigger error ORA-04091
问题描述
执行触发器时出现错误(ORA-04091:表DBPROJEKT_AKTIENDEPOT.AKTIE正在变异，触发器/函数可能看不到):
I get an error (ORA-04091: table DBPROJEKT_AKTIENDEPOT.AKTIE is mutating, trigger/function may not see it) when executing my trigger:
CREATE OR REPLACE TRIGGER Aktien_Bilanz_Berechnung
AFTER
INSERT OR UPDATE OF TAGESKURS
OR INSERT OR UPDATE OF WERT_BEIM_EINKAUF
ON AKTIE
FOR EACH ROW
DECLARE
bfr number;
Begin
bfr := :new.TAGESKURS - :new.WERT_BEIM_EINKAUF;
UPDATE AKTIE
SET BILANZ = TAGESKURS - WERT_BEIM_EINKAUF;
IF bfr < -50
THEN
DBMS_OUTPUT.PUT_LINE('ACHTUNG: The value (Nr: '||:new.AKTIEN_NR||') is very low!');
END IF;
END;
计算值"BILANZ"是否小于-50，我想对其进行检查. 您知道为什么会引发此错误吗?
I want to check the value "BILANZ" after calculating it, wether it is under -50. Do you have any idea why this error is thrown?
感谢您的帮助！
推荐答案
这里有几个问题:
-
Oracle不允许您对在该表上定义的行触发器或从该触发器调用的任何代码中的表执行SELECT/INSERT/UPDATE/DELETE，这就是为什么在运行时发生错误的原因.有多种方法可以解决此问题-例如，您可以阅读我对此问题的答案和这个问题-但总的来说，您会必须避免从触发器内部访问定义了行触发器的表.
Oracle does not allow you to perform a SELECT/INSERT/UPDATE/DELETE against a table within a row trigger defined on that table or any code called from such a trigger, which is why an error occurred at run time. There are ways to work around this - for example, you can read my answers to this question and this question - but in general you will have to avoid accessing the table on which a row trigger is defined from within the trigger.
在此触发器中执行的计算称为业务逻辑，因此不应在触发器中执行.将这样的逻辑放入触发器中，无论它看起来多么方便，最终都会使必须维护该代码的任何人感到困惑，因为BILANZ的值在正在读取应用程序代码的位置被更改了INSERT或UPDATE语句看不到.该计算应在INSERT或UPDATE语句中执行，而不是在触发器中执行.定义一种对表执行INSERT/UPDATE/DELETE操作的过程是一种好的做法，这样所有这些计算都可以在一个地方捕获，而不必分散在整个代码库中.
The calculation which is being performed in this trigger is what is referred to as business logic and should not be performed in a trigger. Putting logic such as this in a trigger, no matter how convenient it may seem to be, will end up being very confusing to anyone who has to maintain this code because the value of BILANZ is changed where someone who is reading the application code's INSERT or UPDATE statement can't see it. This calculation should be performed in the INSERT or UPDATE statement, not in a trigger. It considered good practice to define a procedure to perform INSERT/UPDATE/DELETE operations on a table so that all such calculations can be captured in one place, instead of being spread out throughout your code base.
在BEFORE ROW触发器内，您可以修改:NEW行变量中的字段的值，以在将值写入数据库之前对其进行更改.有时这是可以接受的，例如设置列来跟踪上次更改的时间和对象的列时，但总的来说，这是个坏主意.
Within a BEFORE ROW trigger you can modify the values of the fields in the :NEW row variable to change values before they're written to the database. There are times that this is acceptable, such as when setting columns which track when and by whom a row was last changed, but in general it's considered a bad idea.
好运.
这篇关于Oracle触发错误ORA-04091的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
