MySQL触发器在插入后更新计数

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...

我需要一些mysql触发器的帮助。我目前正在尝试整理一些代码并将一些处理卸载到mysql数据库，而不是运行2个查询和连接等...

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.

所以你指定了类别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;

2 个解决方案

#1

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

您的where子句重复且含糊不清。如果我理解您的数据模型正确，请尝试：

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;

#2

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;

#1