0

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

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;
/
  • When i add a semicolon after sydate, my software percieves the command me trying to run 2 sql statements, where then 2nd simply being the line END; – Levicia Jan 9 '19 at 12:17
  • @WernfriedDomscheit Yes, I did not catch that. Fixed it. – Balazs Papp Jan 9 '19 at 12:22
  • I don't know if you should skip (), i.e. :new.DELETEDTIME := sysdate; – Wernfried Domscheit Jan 9 '19 at 12:23
  • with above code, AQT (Advanced Query Tool) believes im writing 3 different statements, because END; and / are seperated from the query with the semicolon after sysdate – Levicia Jan 9 '19 at 12:29
  • Looking at the AQT Feature List it seems to support querying (obvs) and indirectly other DML statements but there's no mention of DDL. So it seems if you want to change the schema you'll need to use a different tool. – APC Jan 11 '19 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.