Error when using else if in sql trigger
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$$