MySQL 触发器语法和 IF ELSE THEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5271997/
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
Trigger syntax and IF ELSE THEN
提问by Franquis
I'd like to create a trigger which count the number of rows with a specific id (id_ort). If it found more than 5 rows, I need to increment a variable.
我想创建一个触发器来计算具有特定 id (id_ort) 的行数。如果发现超过 5 行，我需要增加一个变量。
Trigger Syntax
触发语法
BEGIN
DECLARE nb INT;
DECLARE nba INT;
SET nba =0;
SET NEW.`VPLS_ID_NodeB` = CONCAT("21100", LPAD(NEW.`VPLS_ID_NodeB`,4,0));
SET nb = (SELECT COUNT(DISTINCT(`VPLS_ID_aggregation`)) FROM `VPLS_nodeB` WHERE `id_ORT` = NEW.`id_ORT`);
IF(nb > 5) THEN
SET nba = nb + 1;
ELSE
SET nba = nb;
END IF;
SET NEW.`VPLS_ID_aggregation` = CONCAT("21188", LPAD(NEW.`id_ORT`,2,0), LPAD(nba,2,0));
END
However, there is a bug... Even if i've less than 5 rows, the var is incremented each time.
但是，有一个错误...即使我少于 5 行，每次都会增加 var。
Any ideas? Maybe it's a syntax problem...
有任何想法吗？可能是语法问题...
Thanks a lot!
非常感谢！
回答by Jon Black
you probably forgot to specify a delimiter i've also made a few other changes as you can see
您可能忘记指定分隔符了，正如您所看到的，我还做了一些其他更改
delimiter #
create trigger VPLS_nodeB_before_ins_trig before insert on VPLS_nodeB
for each row
BEGIN
DECLARE nb INT default 0;
DECLARE nba INT default 0;
SET NEW.VPLS_ID_NodeB = CONCAT('21100', LPAD(NEW.VPLS_ID_NodeB,4,0));
SET nb = (SELECT COUNT(DISTINCT(VPLS_ID_aggregation)) FROM VPLS_nodeB WHERE id_ORT = NEW.id_ORT);
IF(nb > 5) THEN
SET nba = nb + 1;
ELSE
SET nba = nb;
END IF;
SET NEW.VPLS_ID_aggregation = CONCAT('21188', LPAD(NEW.id_ORT,2,0), LPAD(nba,2,0));
END#
delimiter ;
