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?

Disposed

Answer 1

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$$

Orchidaceous

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

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

My apologies - I did not click the link – Yonne

Answer 2

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

Cassiecassil