mysql trigger help

Question

using mysql 5.0.32.

I have two tables: 'UserItemVote' and 'ItemStat'.

UserItemVote has:

user_id, item_id, vote(tinyint either 0 for downvote or 1 for upvote)

ItemStat has:

item_id, total_up_vote, total_down_vote

when user vote up/down an item (insert to UserItemVote table), I want to have a trigger tai_UserItemVote to update either total_up_vote or total_down_vote from ItemStat.

so far I have only worked out the update trigger but not the insert trigger.

tai_UserItemVote insert trigger:

// insert to itemstat if row not exist otherwise update itemstat.

tau_UserItemVote update trigger:

UPDATE ItemStat set total_down_vote=total_down_vote+1 
WHERE item_id=NEW.item_id and NEW.vote = 0;  
UPDATE ItemStat set total_up_vote=total_up_vote+1 
WHERE item_id=NEW.item_id and NEW.vote = 1;

Answer 1

INSERT ... ON DUPLICATE KEY UPDATE should do what you need.