Oracle trigger with OLD references

[hallkbrdz]

Our source database (vendor code) has several triggers like the following flagged as incompatible with EDB11. How could these be re-written for EDB?

CREATE OR REPLACE TRIGGER FCOWNER.FC_DENSITOMETER_BEFORE_INS_UPD BEFORE INSERT OR UPDATE
ON fcowner.FC_densitometer
FOR EACH ROW
WHEN (old.densitometer_index IS NULL OR
new.densitometer_number <> old.densitometer_number OR
new.densitometer_name <> old.densitometer_name OR
(new.densitometer_name IS NOT NULL and old.densitometer_name IS NULL) OR
(new.densitometer_name IS NULL AND old.densitometer_name IS NOT NULL) OR
new.system_index <> old.system_index) BEGIN
:new.last_modified := SYSDATE;
END;
/

[chaitalirs · Accepted solution]

Hi hallkbrdz ,

As per Postgres documentation, INSERT triggers cannot refer to OLD Link:

https://www.postgresql.org/docs/11/sql-createtrigger.html

Oracle allows INSERT triggers to refer to OLD values and in this case OLD values will always be 0. But postgres doesn’t allow refer to OLD values for INSERT triggers.

Hope this helps.