Error when using else if in sql trigger

Question

I am not sure whats wrong with my code.

 delimiter $$
 CREATE TRIGGER updateRestaurantAtributes 
 AFTER UPDATE ON fields_data
 FOR EACH ROW BEGIN
 IF (NEW.fieldid = 1) THEN
    UPDATE restaurants
    SET address1 = NEW.data_txt 
    Where rid = NEW.itemid;
 ELSE IF (NEW.fieldid = 2) THEN
    UPDATE restaurants
    SET address2 = NEW.data_txt 
    Where rid = NEW.itemid;
 END IF;
END$$

The above version does not work. It says syntax error near "END"(Last line). But the something works when I use

delimiter $$
CREATE TRIGGER updateRestaurantAtributes 
AFTER UPDATE ON fields_data
FOR EACH ROW BEGIN
IF (NEW.fieldid = 1) THEN
    UPDATE restaurants
    SET address1 = NEW.data_txt 
    Where rid = NEW.itemid;
END IF;
IF (NEW.fieldid = 2) THEN
    UPDATE restaurants
    SET address2 = NEW.data_txt 
    Where rid = NEW.itemid;
END IF;
END$$

I am not sure why. Am I missing something?

Michael Berkowski · Accepted Answer · 2011-12-31 02:53:30Z

27

Instead of ELSE IF, MySQL's syntax uses ELSEIF (without the space).

 delimiter $$
 CREATE TRIGGER updateRestaurantAtributes 
 AFTER UPDATE ON fields_data
 FOR EACH ROW BEGIN
 IF (NEW.fieldid = 1) THEN
    UPDATE restaurants
    SET address1 = NEW.data_txt 
    Where rid = NEW.itemid;
 ELSEIF (NEW.fieldid = 2) THEN
    UPDATE restaurants
    SET address2 = NEW.data_txt 
    Where rid = NEW.itemid;
 END IF;
END$$

Although you might be able to make it work with the space in ELSE IF by adding an additional END IF. By using the space, you effectively initiate a second IF statement, which must be closed independently of the first outer IF statement.

/* Might work */
delimiter $$
 CREATE TRIGGER updateRestaurantAtributes 
 AFTER UPDATE ON fields_data
 FOR EACH ROW BEGIN
 IF (NEW.fieldid = 1) THEN
    UPDATE restaurants
    SET address1 = NEW.data_txt 
    Where rid = NEW.itemid;
 /* Opens a seconds IF block which must be closed */
 ELSE IF (NEW.fieldid = 2) THEN
    UPDATE restaurants
    SET address2 = NEW.data_txt 
    Where rid = NEW.itemid;
  /* Close inner IF block */
  END IF;
 END IF;
END$$

edited Dec 31, 2011 at 2:53

answered Dec 31, 2011 at 2:48

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

3 Comments

Adrian Cornish Over a year ago

Adding link for syntax :-) dev.mysql.com/doc/refman/5.5/en/if-statement.html

Michael Berkowski Over a year ago

@AdrianCornish Thanks it's already in there, linked to the words "MySQL's syntax"

Adrian Cornish Over a year ago

My apologies - I did not click the link

ANIK ISLAM SHOJIB · Accepted Answer · 2020-02-05 11:08:28Z

0

In Mysql Workbench 8.0 click settings icon bedside the table and there you will find trigger tab. Create Trigger according to your requirement, following if-else syntax will work perfectly

CREATE DEFINER=`root`@`localhost` TRIGGER `users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW BEGIN

 IF (NEW.status_active = 1) THEN
    UPDATE broadcast_status SET `type` = 0, `status` = 0 WHERE user_id = NEW.id;

 ELSE 
        IF (NEW.status_active = 2) THEN
            UPDATE broadcast_status SET `type` = 0, `status` = 2 WHERE user_id = NEW.id;
        ELSE 
            UPDATE broadcast_status SET `type` = 0, `status` = 1 WHERE user_id = NEW.id;
        END IF;
 END IF;

END

answered Feb 5, 2020 at 11:08

ANIK ISLAM SHOJIB

3,3383 gold badges31 silver badges48 bronze badges

Collectives™ on Stack Overflow

Error when using else if in sql trigger

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related