CODEWITHSUNDEEP
mysqlsqltriggers
Padraig
Padraig

Reputation: 3717

How to update multiple tables and fields in a Trigger?

I have the following schema : SQL Fiddle.

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?

Upvotes: 0

Views: 100

Answers (1)

peterm
peterm

Reputation: 92785

Are you looking for this?

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

Upvotes: 1

Related Questions