触发语法和IF ELSE THEN [英] Trigger syntax and IF ELSE THEN

问题描述

我想创建一个触发器，该触发器对具有特定ID(id_ort)的行数进行计数. 如果发现多于5行，我需要增加一个变量.

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.

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

但是，有一个错误……即使我少于5行，每次也会增加var.

However, there is a bug... Even if i've less than 5 rows, the var is incremented each time.

有什么想法吗?也许是语法问题...

Any ideas? Maybe it's a syntax problem...

非常感谢！

推荐答案

您可能忘记了指定定界符，正如您所见，我还做了一些其他更改

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 ;

这篇关于触发语法和IF ELSE THEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！