Mysql Trigger with IF THEN

if-statement, innodb, mysql, triggers

Solution

As far as I can tell both triggers are OK, but you might try the following:

DELIMITER $$
CREATE TRIGGER count_delete_videos BEFORE DELETE ON videos
FOR EACH ROW 
BEGIN
  UPDATE counts SET count = count - 1 WHERE name = 'all';

  IF OLD.published = 1 THEN BEGIN
    DELETE FROM videos_categories WHERE id_video = OLD.id;
    DELETE FROM videos_tags WHERE id_video = OLD.id;
  END; END IF;
END$$

DELIMITER ;

Problem

My storage is INNODB, I'm trying to create an trigger with 2 queries in IF statement. Down you can see the trigger that gives me the error ``` delimiter | CREATE TRIGGER count_delete_videos BEFORE DELETE ON videos FOR EACH ROW BEGIN UPDATE counts SET count = count - 1 WHERE name = 'all'; IF OLD.published = 1 THEN DELETE FROM videos_categories WHERE id_video = OLD.id; DELETE FROM videos_tags WHERE id_video = OLD.id; END IF; END; | delimiter ; 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 '= OLD.id; END IF; END' at line 6 ``` This are the 2 triggers that i activate with the first one. ``` delimiter | CREATE TRIGGER count_delete_videos_tags AFTER DELETE ON videos_tags FOR EACH ROW BEGIN UPDATE tags SET count = count - 1 WHERE id = OLD.id_tag; END; | delimiter ; delimiter | CREATE TRIGGER count_delete_videos_categories AFTER DELETE ON videos_categories FOR EACH ROW BEGIN UPDATE categories SET count = count - 1 WHERE id = OLD.id_category; IF OLD.id_category <> 20 AND OLD.id_category <> 34 THEN UPDATE counts SET count=count-1 WHERE name='english'; ELSEIF OLD.id_category = 34 THEN UPDATE counts SET count=count-1 WHERE name='german'; ELSEIF OLD.id_category = 20 THEN UPDATE counts SET count=count-1 WHERE name='italian'; END IF; END; | delimiter ; ``` But this one works perfectly ``` delimiter | CREATE TRIGGER count_delete_videos BEFORE DELETE ON videos FOR EACH ROW BEGIN UPDATE counts SET count = count - 1 WHERE name = 'all'; IF OLD.published = 1 THEN DELETE FROM videos_categories WHERE id_video = OLD.id; END IF; END; | delimiter ; Query OK, 0 rows affected (0.16 sec) ``` How can i make first trigger work? what i'm doing wrong? Thx for helping me.

Original source