A trigger making a timestamp of when another column was changed

Question

I've beeen trying to create a trigger, that'll make a timestamp, of when our account was "deleted" which we do by setting deleted = 1. Below code creates a trigger for that purporse, however it has compilation errors and doesn't work.

So i wanna update the column DELETEDTIME with the the current sysdate timestamp of when deleted was set equal to 1, however i can't see where i've gone wrong in the code below

CREATE OR REPLACE TRIGGER trg_deletiontime 
AFTER UPDATE 
OF DELETED ON VTIGER_CRMENTITY
FOR EACH ROW
WHEN (old.DELETED = 0 AND old.DELETEDTIME = NULL)
BEGIN
new.DELETEDTIME := sysdate()
END;

Error from sys.user_errors says "PLS-00103: Encountered the symbol "END", however no amount of ; or / seems to fix this

Answer 1

• Semicolon is missing after sysdate().
• new should be :new.
• You can not assign values to :new in an AFTER UPDATE trigger, becasue it fires after the update, the value is already updated. So I changed it to BEFORE UPDATE.

create table vtiger_crmentity (deleted number, deletedtime date);

CREATE OR REPLACE TRIGGER trg_deletiontime 
BEFORE UPDATE 
OF DELETED ON VTIGER_CRMENTITY
FOR EACH ROW
WHEN (old.DELETED = 0 AND old.DELETEDTIME is NULL)
BEGIN
:new.DELETEDTIME := sysdate();
END;
/