如何使用触发器在SQL中递增? [英] How to increment in SQL using a trigger?

问题描述

所以我有一张桌子，上面放有各种美食的清单，以及该美食共有多少家餐厅. (例如，意大利语| 7)

So I have a table that holds a list of cuisines and a total of how many restaurants there are with that cuisine. (e.g. Italian | 7)

我正在尝试在phpMyAdmin中设置一个触发器，该触发器将在每次将新餐厅添加到数据库时增加总数.这是我到目前为止的内容:

I'm trying to set-up a trigger in phpMyAdmin that will increment the total every time a new restaurant is added to the database. Here is what I have so far:

CREATE TRIGGER UpdateStats AFTER INSERT ON Restaurant
BEGIN
    UPDATE RestaurantStats SET TotalRestaurants = TotalRestaurants + 1 WHERE Cusine = NEW.cusine;
END;

但是我一直收到错误消息，并告诉我这与我的语法有关.我要去哪里错了?我遵循了我看到的示例.

But I keep getting an error message and it's telling me that it has to do with my Syntax. Where am I going wrong? I followed the example I saw pretty well.

这是错误消息:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN UPDATE RestaurantStats SET TotalRestaurants = TotalRestaurants + 1 WHERE' at line 2

推荐答案

您需要使用FOR EACH ROW并摆脱BEGIN ... END这样的内容:

You need to use FOR EACH ROW and get rid of the BEGIN...END like this:

CREATE TRIGGER UpdateStats AFTER INSERT ON Restaurant
FOR EACH ROW
    UPDATE RestaurantStats SET TotalRestaurants = TotalRestaurants + 1 WHERE Cusine = NEW.cusine;

BEGIN ... END仅用于同一触发器中的多个查询，并且需要重新定义终止字符.所以你首先;正在终止该语句并使BEGIN处于打开状态而没有END.

BEGIN...END is only used for multiple queries in the same trigger and requires redefinition of the termination character. So your first ; is terminating the statement and leaving the BEGIN open without an END.

这篇关于如何使用触发器在SQL中递增?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！