问题 Mysql触发与IF那么

我的存储是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

这是我用第一个触发的2个触发器。

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 ;

你能为每个删除语句给两次if子句看看吗？ - nawfal

（除了计数器），你知道你可以使用外键约束（每个表一个）到 video 表，通过定义FK ON DELETE CASCADE？ - ypercubeᵀᴹ

是的我知道，但是在预测mysql中的键触发器不会激活，所以我必须用另一个触发器删除它，这样这个查询激活其他触发器 - Alex Prut

这就是我得到的 - > ERROR 1235（42000）：这个版本的MySQL还不支持'多个触发器，具有相同的一个表的动作时间和事件' - Alex Prut

@ P.Alex - 你的MySQL版本是什么？ - Álvaro González

@ P.Alex，显然你必须在添加这个之前删除其他触发器。 - Johan

我编辑了我的帖子。我更好地解释了我的问题，请看一下。谢谢 - Alex Prut

好的，问题解决了。问题是我在同一张桌子上有2个动作触发器。 - Alex Prut

问题 Mysql触发与IF那么

答案:

热门问题