Ok so I'm writing a trigger in sql and what needs to happen is that if t is deleted and f.actDepartDateTime is not null then the table is backed up and then deleted but I can't seem to get it to work?

CREATE TABLE BACKUP_table (
      f_id         number,
      t_number      number,
      s_number number
);



CREATE OR REPLACE TRIGGER t_delete
    BEFORE DELETE ON t IF f.actDepartDateTime Not Null
    FOR EACH ROW
BEGIN

   -- insert a row into the backup table  

   INSERT INTO BACKUP_Table (f_id, t_number, s_number)
   VALUES(old.fid, old.tnum, old.sNum);
END;

CodePudding user response：

• If you want a trigger to fire conditionally, the keyword is when not if.
• The syntax for doing a null comparison is is null or is not null.
• In the trigger body, you need to prefix references to the :old pseudo-record with a colon

I am not sure what f.actDepartureDateTime is supposed to reference-- there is no f alias anywhere in the code you posted. I'm guessing that actDepartureDateTime is a column in the table t and that you want to fire the trigger based on the old value of that column.

Assuming that is what you are looking for, this should work

CREATE OR REPLACE TRIGGER t_delete
    BEFORE DELETE ON t 
    FOR EACH ROW
    WHEN( old.actDepartDateTime IS Not Null )
BEGIN
  INSERT INTO BACKUP_Table (f_id, t_number, s_number)
    VALUES(:old.fid, :old.tnum, :old.sNum);
END;