oracle ORA-04082: 表级触发器中不允许使用 NEW 或 OLD 引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18741342/
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-04082: NEW or OLD references not allowed in table level triggers
提问by Mark Marina
itI have table named per. In the pertable, I have a field named "fl1" and another field named "fl2". When updating a record, I want to check if the value of "fl1" has changed". If the value has changed, update the "fl2" column with the new value from "fl1".
它有名为per 的表。在每个表中，我有一个名为“fl1”的字段和另一个名为“fl2”的字段。更新记录时，我想检查“fl1”的值是否已更改。如果值已更改，请使用“fl1”中的新值更新“fl2”列。
I came up with this trigger
我想出了这个触发器
CREATE OR REPLACE TRIGGER Flag
AFTER INSERT OR UPDATE on per
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
If :New.fl1 != :Old.fl1 Then
:New.fl2:= :new.fl1;
End If;
END;
I get a "ORA-04082: NEW or OLD references not allowed in table level triggers" when I run it
运行时出现“ORA-04082：表级触发器中不允许使用新的或旧的引用”
The other option I was thinking about (not sure if it would be efficient), is to simply update the value of "fl2" with the value of "fl1" regardless if the value of "fl1" has changed.
我正在考虑的另一个选项（不确定它是否有效）是简单地将“fl2”的值更新为“fl1”的值，而不管“fl1”的值是否已更改。
UPDATE
更新
Added a "For Each Row" and changed "AFTER INSERT OR UPDATE" for "BEFORE INSERT OR UPDATE". It's working.
添加了“For Each Row”并将“AFTER INSERT OR UPDATE”更改为“BEFORE INSERT OR UPDATE”。它正在工作。
CREATE OR REPLACE TRIGGER Flag
BEFORE INSERT OR UPDATE on per
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
If :New.fl1 != :Old.fl1 Then
:New.fl2:= :new.fl1;
End If;
END;
采纳答案by David Aldridge
DML triggers are either defined as table level or as row level.
DML 触发器定义为表级别或行级别。
A table level trigger fires once for each operation on the table, so if you update 30 rows then that is one operation as far as a table trigger is concerned. Table triggers do not have insight into which rows are modified, but can be used to log the fact that an operation was carried out.
表级触发器为表上的每个操作触发一次，因此如果您更新 30 行，那么就表触发器而言，这就是一个操作。表触发器无法了解哪些行被修改，但可用于记录执行操作的事实。
In this case you need a row level trigger, which requires "FOR EACH ROW" to be included in the trigger definition. The "REFERENCING" clause is optional if you do not want to change the way that you reference the new and old rows.
在这种情况下，您需要一个行级触发器，它需要在触发器定义中包含“FOR EACH ROW”。如果您不想更改引用新行和旧行的方式，则“REFERENCING”子句是可选的。
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#BABCIBBJ
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#BABCIBBJ
Not sure what the point of the exercise here is, though. Have you considered just referencing fl1 instead of fl2?
不过，不确定这里练习的重点是什么。您是否考虑过只引用 fl1 而不是 fl2？
回答by Mark Marina
Final working code:
最终工作代码：
CREATE OR REPLACE TRIGGER Flag
BEFORE INSERT OR UPDATE on per
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
If :New.fl1 != :Old.fl1 Then
:New.fl2:= :new.fl1;
End If;
END;
