mysql - 触发器更新另一个表的计数器

Question

似乎无法让这个触发器工作...一些帮助将不胜感激。

DROP TRIGGER IF EXISTS trigger_votes;

CREATE TRIGGER trigger_votes AFTER INSERT ON votes
UPDATE collections
SET IF(vote = 1, loves, hates) = IF(vote = 1, loves, hates) + 1
WHERE collections.id = NEW.collection_id;

当新的“投票”记录在表投票中时，我想增加表集合中包含计数的列“喜欢”或“讨厌”。 votes 表有一个名为 collection_id 的列，它对应于 collections 表中的一条记录。

Answer 1

DELIMITER $$

CREATE TRIGGER trigger_votes AFTER INSERT ON votes FOR EACH ROW
BEGIN
  UPDATE collections
    SET loves = CASE NEW.vote WHEN 1 THEN loves + 1 ELSE loves END
        ,hates = CASE NEW.vote WHEN 1 THEN hates ELSE hates + 1 END 
    WHERE collections.id = NEW.collection_id;
END $$

DELIMITER ;