我在使用sql创建触发器的代码中存在语法错误。我使用mysql版本14.14 [英] I have a syntax error in my code for creating a trigger using sql. I use mysql version 14.14

问题描述

我的触发器中有语法错误，我不知道为什么！我在一家销售公司的数据库中有3个表，分别是ORDERS，SALESREPS和PRODUCTS。每当下订单时，我都必须将该订单添加到ORDERS表中。订单成本将添加到SALESREPS表的SALES列中。请注意，salesreps是销售产品的人的表，列销售存储了各个销售员的销售总额。此外，表PRODUCTS（该表包含所有产品及其详细信息的列表）中包含一列数量。因此，在下订单时，我必须从下订单前减少可用数量。

Hey Guys I have a syntax error in my trigger I dont know why! I have 3 tables in a database for a sales company, namely ORDERS,SALESREPS AND PRODUCTS. Whenever an order is placed, I have to add that order to ORDERS table. The cost of the order is added to the SALES column in SALESREPS table. Note that salesreps is a table of people who sell products and the column sales stores total amount of sales by respective salesperson. Also, The table PRODUCTS( which is a table having a list of all products and their details) contains a column 'quantity in hand'. SO when an order is placed, I have to reduce the number of orders from the quantity available before order of that product was placed.

我收到语法错误，

错误1064（42000）：您的SQL语法有错误；请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法，以在第2行的在插入AS后立即订购AS
更新SALESREPS
SET SALES = SALES + INSERTED.AMOUNT附近使用

ERROR 1064 (42000): 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 'ON ORDERS AFTER INSERT AS UPDATE SALESREPS SET SALES=SALES+INSERTED.AMOUNT' at line 2

这是我的触发器代码：

CREATE TRIGGER test_trigger
ON ORDERS AFTER INSERT AS
    UPDATE SALESREPS
    SET SALES=SALES+INSERTED.AMOUNT
        FROM SALESREPS,INSERTED
    WHERE SALESREPS.EMPL_NUM=INSERTED.REP;
    UPDATE PRODUCTS
    SET QTY_ON_HAND=QTY_IN_HAND - INSERTED.QTY
        FROM PRODUCTS,INSERTED
        WHERE PRODUCTS.MFR_ID=INSERTED.MFR
            AND PRODUCTS.PRODUCT_ID=INSERTED.PRODUCT;

此外，由于我是根据插入的元组进行更新的表，因此如何引用插入的元组

Also, since my am updatin tables based on a inserted tuple , how do I refer to the inserted tuple??

推荐答案

您在这里有多个语法问题，但我认为这就是您所需要的

You have multiple syntax problem here, but I think this is what you need

DELIMITER $$
CREATE TRIGGER test_trigger
AFTER INSERT ON ORDERS
FOR EACH ROW BEGIN
    UPDATE SALESREPS
       SET SALES=SALES+NEW.AMOUNT
     WHERE SALESREPS.EMPL_NUM=NEW.REP;
    UPDATE PRODUCTS
       SET QTY_ON_HAND= (QTY_IN_HAND - NEW.QTY)
     WHERE PRODUCTS.MFR_ID=NEW.MFR_ID
       AND PRODUCTS.PRODUCT_ID=NEW.PRODUCT;
END;$$
DELIMITER ;

您的第一个问题是此行：

Your first problem was with this line :

ON ORDERS AFTER INSERT AS

恢复了哪个

AFTER INSERT ON ORDERS

关于我删除 AS 的通知。

您也错过了对于每个行开始/结束

请注意，在开始时我将分隔符指定为<​​code > $$ ，以便编译器知道何时停止。

Notice that at the beginning I specify the delimiters to be $$ so the compiler know when to stop.

此外， update 语句一定不能有 from 子句，因为您已经指定了要在第一条语句上更新的表。

Also, update statement must not have a from clause, as you already specify which table you are updating on the first statement.

UPDATE SALESREPS

下一个问题是，当您尝试引用新值时，要访问的指定关键字就像 new 这样简单。

Next problem is when you tried to refer to the new value, well the specified keyword to access is is as simple as new.

您的最后一个错误是您的产品的状况。 mfr_id = new.mfr 您可能只是在这里打错了，要使用其中products.mfr_id = new.mfr_id

Your last error was your condition where products.mfr_id = new.mfr you probably just made a typo here, you want to use where products.mfr_id = new.mfr_id

这篇关于我在使用sql创建触发器的代码中存在语法错误。我使用mysql版本14.14的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！