Getting 1064 error while creating mysql trigger

Question

I am trying to create mysql trigger but getting an error

Error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 14

Here is my sql script

delimiter $$
CREATE TRIGGER `update_account_balance_after_transfer` AFTER INSERT ON `balance_transfer`
 FOR EACH ROW  
    BEGIN
    IF(NEW.from_account_type='CUSTOMER') THEN
        UPDATE customer c SET c.balance = c.balance-NEW.amount WHERE c.customer_id= NEW.from_account_id;
    ELSE IF(NEW.from_account_type='Vendor') THEN
        UPDATE vendor V SET v.balance = v.balance-NEW.amount WHERE v.vendor= NEW.from_account_id;
    END IF;
    IF(NEW.to_account_type='VENDOR') THEN
        UPDATE customer c SET c.balance = c.balance+NEW.amount WHERE c.customer_id= NEW.to_account_id;
    ELSE IF(NEW.to_account_type='Vendor') THEN
        UPDATE vendor V SET v.balance = v.balance+NEW.amount WHERE v.vendor= NEW.to_account_id;
    END IF;
END $$
delimiter ;

zarruq · Accepted Answer · 2017-10-20 04:44:33Z

2

Remove space between ELSE and IF. It should be ELSEIF as below.

delimiter $$
CREATE TRIGGER `update_account_balance_after_transfer` AFTER INSERT ON `balance_transfer`
 FOR EACH ROW  
    BEGIN
    IF(NEW.from_account_type='CUSTOMER') THEN
        UPDATE customer c SET c.balance = c.balance-NEW.amount WHERE c.customer_id= NEW.from_account_id;
    ELSEIF(NEW.from_account_type='Vendor') THEN
        UPDATE vendor V SET v.balance = v.balance-NEW.amount WHERE v.vendor= NEW.from_account_id;
    END IF;
    IF(NEW.to_account_type='VENDOR') THEN
        UPDATE customer c SET c.balance = c.balance+NEW.amount WHERE c.customer_id= NEW.to_account_id;
    ELSEIF(NEW.to_account_type='Vendor') THEN
        UPDATE vendor V SET v.balance = v.balance+NEW.amount WHERE v.vendor= NEW.to_account_id;
    END IF;
END $$
delimiter ;

MySQL IF Syntax

Check the demo here

answered Oct 20, 2017 at 4:44

zarruq

2,4552 gold badges12 silver badges20 bronze badges

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

1 Comment

Muhammad Imran Over a year ago

Oh yes, my bad, actually i have removed those spaces earlier, then later I update sql statement and copied old code from notepad. Thanks btw.. :)

Collectives™ on Stack Overflow

Getting 1064 error while creating mysql trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related