Mysql Trigger with IF THEN
82,949
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 ;
Author by
Alex P.
Updated on May 25, 2020Comments
-
Alex P. almost 4 years
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.
-
Alex P. about 12 yearsThis is what i get -> ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
-
Álvaro González about 12 years@P.Alex - And what's your MySQL version?
-
Johan about 12 years@P.Alex, obviously you'll have to delete the other trigger before adding this one.
-
Alex P. about 12 yearsi have edited my post. I explained better my problem, please take a look. Thx
-
Alex P. about 12 yearsOK, problem solved. The problem was that i had 2 action trigger on the same table.