Using an If statement to assign a variable within a Trigger

Question

I'm trying to assign a value to a variable based on an if statement to be used to insert into a table as part of a trigger

I'm trying to assign a value to a variable based if another variable that is calculated is great than zero.

 CREATE OR REPLACE TRIGGER transactions_checking_trigger
   AFTER INSERT OR UPDATE ON checking
   FOR EACH ROW
 DECLARE
   account_type        char(4);     
   account_num         char(4);           
   trans_date          date;             
   trans_amt           number;            
   trans_type          char(4);            
   trans_comments      varchar(25);    
 BEGIN

   account_type    := 'CHEK';
   trans_date      := sysdate;
   trans_amt       := :new.balance-:old.balance;

   if trans_amt > 0 
      trans_type := 'DEPT'
   ELSE
      trans_type := 'WDRW'
   end if;

   IF UPDATE THEN
      INSERT INTO transactions (account_type, account_num, trans_date, 
      trans_amt, trans_type,trans_comments)
      VALUES (account_type, :new.account_num,trans_date,trans_amt, trans_type, 
      new:trans_comments);
   END IF;
END;
/

I expect the trigger to insert DEPT if trans_amt > 0 and WDRW if trans_amt < 0

What is the current behavior? Are you getting compilation or runtime errors? — Tad Harrison
– Tad Harrison, Commented Aug 20, 2019 at 19:42
I haven't ran it because I'm getting a syntax error with the IF statement. — Matt
– Matt, Commented Aug 20, 2019 at 19:55

Littlefoot · Accepted Answer · 2019-08-20 20:12:07Z

1

Syntax is IF-THEN-ELSE; you're missing the THEN. Also, statement has to be terminated with a semi-colon.

if trans_amt > 0 
then                        --> this
   trans_type := 'DEPT';    --> semi-colon
ELSE
   trans_type := 'WDRW';    --> semi-colon
end if;

A simpler option:

trans_type := case when trans_amt > 0 then 'DEPT' 
                   else 'WDRW'
              end;

This is wrong:

IF UPDATE THEN

Did you mean updating instead?

answered Aug 20, 2019 at 20:12

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Using an If statement to assign a variable within a Trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related