CODEWITHSUNDEEP
mysqltriggers
Tony
Tony

Reputation: 825

MySQL Trigger to update count after insert

I need some help with a mysql trigger. I am currently trying to tidy up some code and offload some processing to the mysql database rather than running 2 queries and joins etc...

When someone inserts into a video into a database, I would like the video count in the category to update.

So you specify the category id.

This is what I have so far so any help would be good as i just dont seem to be able to get the syntax correct.

CREATE TRIGGER 'db_videos'.'update_video_count'
AFTER INSERT ON 'videos' FOR EACH ROW
BEGIN
    UPDATE video_cat WHERE video_count = video_count + 1 WHERE video_cat = video_cat;
END;

Upvotes: 1

Views: 7266

Answers (2)

Vishal Dhiman
Vishal Dhiman

Reputation: 21

try the following:

CREATE TRIGGER 'db_videos'.'update_video_count'
AFTER INSERT ON 'videos' FOR EACH ROW
BEGIN
    UPDATE video_cat vc 
      SET video_count = video_count + 1 
      WHERE NEW.video_cat = vc.video_cat;
END;

Upvotes: 2

Tim Hoolihan
Tim Hoolihan

Reputation: 12396

Your where clause is duplicated and ambiguous. If I understand your data model correct, try:

CREATE TRIGGER 'db_videos'.'update_video_count'
AFTER INSERT ON 'videos' FOR EACH ROW
BEGIN
    UPDATE video_cat vc 
      WHERE video_count = video_count + 1 
      and NEW.video_cat = vc.video_cat;
END;

Upvotes: 1

Related Questions