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屋！
