MySQL 在 sql 触发器中使用 else if 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8685957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error when using else if in sql trigger
提问by user899893
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
上面的版本不行。它说“END”附近的语法错误（最后一行）。但是当我使用时有些东西有效
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?
我不知道为什么。我错过了什么吗？
回答by Michael Berkowski
Instead of ELSE IF, MySQL's syntaxuses ELSEIF(without the space).
而不是ELSE IF，MySQL 的语法使用ELSEIF（没有空格）。
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 IFby adding an additional END IF. By using the space, you effectively initiate a second IFstatement, which must be closed independently of the first outer IFstatement.
尽管您可以ELSE IF通过添加额外的END IF. 通过使用空格，您可以有效地启动第二个IF语句，该语句必须独立于第一个外部IF语句关闭。
/* 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$$
回答by king neo
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
在 Mysql Workbench 8.0 中，单击表旁边的设置图标，您将在那里找到触发器选项卡。根据您的要求创建触发器，遵循 if-else 语法将完美运行
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
