Trigger syntax and IF ELSE THEN

Question

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.

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.

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

Thanks a lot!

Jon Black · Accepted Answer · 2011-03-11 11:14:24Z

8

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 ;

answered Mar 11, 2011 at 11:14

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Franquis Over a year ago

Actually, I specified a delimiter. I tried with your changes but it's still not working :( Thanks anyway ^^

Baskar PC Over a year ago

I added the delimiter as $$ and it did the trick according to this post stackoverflow.com/questions/10258394/…

Collectives™ on Stack Overflow

Trigger syntax and IF ELSE THEN

Trigger Syntax

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Trigger Syntax

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related