ORA-04082: NEW or OLD references not allowed in table level triggers

Question

itI have table named per. In the per table, 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".

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

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.

UPDATE

Added a "For Each Row" and changed "AFTER INSERT OR UPDATE" for "BEFORE INSERT OR UPDATE". It's working.

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; 

Answer 1

DML triggers are either defined as table level or as row level.

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.

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.

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?

Answer 2

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;