Multiple conditionals inside a trigger using SQL

Question

I am trying to create a trigger that will update a table called VIDEO after the table DETAILRENTAL is updated. I am trying to use IF ELSE conditionals to satisfy multiple situations.

This is what I have coded:

CREATE OR REPLACE TRIGGER TRG_VIDEORENTAL_UP
AFTER UPDATE OF DETAIL_RETURNDATE ON DETAILRENTAL
FOR EACH ROW
BEGIN  
 IF :NEW.DETAIL_RETURNDATE IS NULL THEN
       UPDATE VIDEO
       SET VID_STATUS = 'OUT'
       WHERE :NEW.VID_NUM = VID_NUM;

ELSEIF
  :NEW.DETAIL_RETURNDATE > SYSDATE
       UPDATE VIDEO
       SET VID_STATUS = 'OUT'
       WHERE:NEW.VID_NUM = VID_NUM;

ELSEIF
  :NEW.DETAIL_RETURNDATE < SYSDATE OR :NEW.DETAIL_RETURNDATE = SYSDATE 
       UPDATE VIDEO
       SET VID_STATUS = 'IN'
       WHERE :NEW.VID_NUM = VID_NUM;

ELSE 
   :NEW.DETAIL_RETURNDATE = '01/01/01'
       UPDATE VIDEO
       SET VID_STATUS = 'LOST'
       WHERE :NEW.VID_NUM = VID_NUM ;

  END IF;
end;

This is the error message I get when I run this code:

Error at line 8: PLS-00103: Encountered the symbol "" when expecting one of the following: := . ( @ % ;

I already tested each conditional to ensure that each statement works individually but it does not seem to work together.

Gordon Linoff · Accepted Answer · 2018-03-19 17:29:41Z

1

Oracle uses ELSIF, not ELSEIF for multiple conditions.

That is probably the cause of the error.

answered Mar 19, 2018 at 17:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2018-03-19 18:42:30Z

In addition to ELSEIF needing to be ELSIF as @Gordon pointed out, those ELSIF conditions still need a THEN:

ELSIF
  :NEW.DETAIL_RETURNDATE > SYSDATE THEN
       UPDATE VIDEO
       ...
ELSIF
  :NEW.DETAIL_RETURNDATE < SYSDATE OR :NEW.DETAIL_RETURNDATE = SYSDATE THEN
       UPDATE VIDEO
       ...

and your final ELSE either needs to be an ELSIF plus THEN:

ELSIF 
   :NEW.DETAIL_RETURNDATE = '01/01/01' THEN
       UPDATE VIDEO
       ...

or remove that condition:

ELSE
       UPDATE VIDEO
       ...

depending on the logic you actually need.

If you do need the condition, and thus the ELSIF, then the way you are comparing the date in that condition is wrong too; '01/01/01' is a string not a date, so you are relying on implicit conversion using the session's NLS settings, which you may not control. Use to_date() with a suitable format mask, or an ANSI date literal:

ELSIF 
   :NEW.DETAIL_RETURNDATE = DATE '2001-01-01' THEN
       UPDATE VIDEO
       ...

Collectives™ on Stack Overflow

Multiple conditionals inside a trigger using SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related