Oracle trigger with OLD references

Accepted Solution

Oracle trigger with OLD references

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;
/

Accepted Solutions (1)

Accepted Solutions (1)

EDB Team Member
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.

 

Answers (0)

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks