MySQL Trigger Question

Question

I have a mysql table called books that has a "votes" column, and another table called series which has a "total_votes" column. The votes column tracks votes for an individual book, while the total_votes column is a tabulation of all votes for all books within a series.

In the administration section, an admin can manually change the number of votes an individual book has. I want to create a mysql trigger so that whenever a vote is changed, the difference between old vote value and new vote value is reflected in the total_votes column. For example, if Book A has 10 votes and Series 1 has a total of 100 votes, when I change the vote count of Book A to 5, I want the total_vote count of Seris 1 to be 95.

I understand how to make a basic trigger, but I'm not sure how update the Series table with the difference between the old and new vote values.

TIA.

Can you give us the schema (DESC of the table(s) in question)? — ThinkCode
– ThinkCode, Commented Aug 16, 2011 at 16:16

ChrisLively · Accepted Answer · 2011-08-16 16:22:13Z

1

Don't worry about the difference, just update based on a new sum. For example:

(Not sure if this is the exact MySql syntax)

update series
set total_votes = sum(select votes from books b where b.seriesid = seriesid)

answered Aug 16, 2011 at 16:22

ChrisLively

88.3k27 gold badges174 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Trigger Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related