本文介绍了如何在触发器中更新多个表和字段?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我具有以下架构:。
触发器正在更新articles.votes字段。我还需要它来使用与更新article.votes字段相同的公式来更新members.points字段和members life_points字段。我该怎么办?
The trigger is updating the articles.votes field. I also need it to update the members.points field and the members lifetime_points fields with the same formula as updates the articles.votes field. How do I do that?
推荐答案
您在寻找这个吗?
DELIMITER $$
CREATE TRIGGER tg_ai_article_votes
AFTER INSERT ON article_votes
FOR EACH ROW
BEGIN
UPDATE articles
SET votes = COALESCE(votes, 0) + 1
WHERE id = NEW.article_id;
UPDATE members
SET points = COALESCE(points, 0) + 1,
lifetime_points = COALESCE(lifetime_points, 0) + 1
WHERE id = NEW.member_id;
END$$
CREATE TRIGGER tg_ad_article_votes
AFTER DELETE ON article_votes
FOR EACH ROW
BEGIN
UPDATE articles
SET votes = COALESCE(votes, 0) - 1
WHERE id = OLD.article_id;
UPDATE members
SET points = COALESCE(points, 0) - 1,
lifetime_points = COALESCE(lifetime_points, 0) - 1
WHERE id = OLD.member_id;
END$$
DELIMITER ;
这里是 演示
Here is SQLFiddle demo
这篇关于如何在触发器中更新多个表和字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!