Create trigger to set column value depending on another table column value in Oracle database

You can create a trigger in Oracle database to set column value dynamically depending on another table column value. Recently some asked this question in Oracle Apex Forum and here was my suggestion for creating trigger to check if the new value exists in another table column and set another column value to OK or ERROR.

Here is the example code –

CREATE OR REPLACE TRIGGER RTG_NAME
   BEFORE INSERT
   ON pf_po_inbox
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW

DECLARE
   BANK_ID   NUMBER;
BEGIN
   SELECT BANK_ID
     INTO BANK_ID
     FROM pf_memberbanks
    WHERE BANK_ID = :NEW.DB_BANK_ID;

   IF BANK_ID IS NOT NULL
   THEN
      -- set the column value if the BANK_ID exists
      :NEW.PO_SB_MESSAGE := 'OK';
   ELSE
      :NEW.PO_SB_MESSAGE := 'ERROR';
   END IF;
END;

To view this forum post please visit – https://forums.oracle.com/forums/thread.jspa?messageID=10928908&#10928908