CODEWITHSUNDEEP
mysqlsqltriggers
art
art

Reputation: 306

syntax error when using case / if/else inside mysql trigger

I am using a case statement in my MySQL trigger. But getting a syntax error when using a case block / if/else inside a trigger in MySQL.

USING CASE

DELIMITER //
CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT ON `tb_tickets`
       FOR EACH ROW 
BEGIN
 CASE NEW.program_id
 WHEN 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 3 THEN
   UPDATE tb_sites_3
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 END
END
DELIMITER ;

error Sorry an unexpected error happened!

USING IF/ELSE

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT ON `tb_tickets`
       FOR EACH ROW 
BEGIN
 IF NEW.program_id = 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ELSEIF NEW.program_id = 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ELSE
  UPDATE tb_sites_3
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 END
END;

error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9

Upvotes: 0

Views: 324

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Always best to read the whole comment. You missed the end case; and end // (for the begin)

DELIMITER //
CREATE TRIGGER T
       AFTER INSERT ON `tb_tickets`
       FOR EACH ROW 
BEGIN
 CASE NEW.program_id
 WHEN 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 3 THEN
   UPDATE tb_sites_3
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 END CASE;
END //
DELIMITER ;

If you have any other errors please add the full error.

Upvotes: 1

Related Questions