MySQL触发器在插入后更新计数 [英] MySQL Trigger to update count after insert
问题描述
我需要有关mysql触发器的一些帮助。我目前正在尝试整理一些代码并将部分处理工作转移到mysql数据库中,而不是运行2个查询和联接等...
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.
因此,您可以指定类别ID。
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;
推荐答案
您的where子句重复且不明确。如果我理解您的数据模型正确,请尝试:
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;
这篇关于MySQL触发器在插入后更新计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!