作者热门文章
- mongodb - 在 MongoDB mapreduce 中,如何展平值对象?
- javascript - 对象传播与 Object.assign
- html - 输入类型 ="submit"Vs 按钮标签它们可以互换吗?
- sql - 使用 MongoDB 而不是 MS SQL Server 的优缺点
我的存储是 INNODB,我正在尝试在 IF 语句中创建一个带有 2 个查询的触发器。向下你可以看到给我错误的触发器
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
这是我用第一个触发的两个触发器。
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 ;
但这一款效果很好
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)
如何使第一个触发器工作?我做错了什么?谢谢你帮助我。
最佳答案
据我所知,两个触发器都可以,但您可以尝试以下方法:
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 ;
关于MySQL 触发器与 IF THEN,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10374454/
我是一名优秀的程序员,十分优秀!