子查询返回了 1 个以上的值.当子查询跟随 = 或子查询用作表达式时，这是不允许的?

2022-01-01 00:00:00 tsql sql-server triggers

我想在一个表中插入值后更新多个表和值，所以我创建了一个触发器.它适用于插入一行，但一旦我插入更多行，SQL Server 就会出现以下错误:

I want to update multiple tables and values after inserting values in one table so I created a trigger. It works fine for inserts of one row, but as soon I insert more rows, SQL Server gives me following error:

子查询返回了 1 个以上的值.当子查询跟随 = 或当子查询用作表达式时，这是不允许的?

subquery returned more than 1 value. this is not permitted when the subquery follows = or when the subquery is used as an expression?

这是我的触发器:

CREATE TRIGGER [dbo].[tbl_Sales_ForInsert]
ON [dbo].[SALES] 
FOR INSERT
AS
BEGIN
  DECLARE @ITEMMODEL varchar(100)

  SELECT @ITEMMODEL = ITEM_MODEL FROM inserted

  UPDATE SALES 
  SET PROFIT = TOTAL_PRICE - (SELECT QUANTITY FROM SALES WHERE ITEM_MODEL = @ITEMMODEL) * (SELECT RATE FROM ITEM_DETAILS WHERE ITEM_MODEL = @ITEMMODEL) 
  WHERE ITEM_MODEL = @ITEMMODEL

  UPDATE ITEM_DETAILS 
  SET QUANTITY = QUANTITY - (SELECT QUANTITY FROM SALES WHERE ITEM_MODEL = @ITEMMODEL) 
  WHERE ITEM_MODEL = @ITEMMODEL

  --UPDATE ITEM_DETAILS SET AMOUNT = AMOUNT - (SELECT RATE FROM ITEM_DETAILS WHERE ITEM_MODEL=@ITEMMODEL) * (SELECT QUANTITY FROM SALES WHERE ITEM_MODEL=@ITEMMODEL) where ITEM_MODEL=@ITEMMODEL
END

当我第一次在 SALES 表中插入数据时，更新成功但第二次它给了我上述错误，记住 ITEM_MODEL 是 SALES 表中的外键约束.

As I insert data in SALES table for 1st time the update got successful but for 2nd time it gives me above error remember ITEM_MODEL is foreign key constraint in SALES table.

我一直遇到这个错误，有人可以帮我吗?

I have been suffering with this error can anyone help me please?

推荐答案

你的根本缺陷是你似乎期望触发器被触发每行一次 - 这不是强> SQL Server 中的情况.相反，触发器每个语句触发一次，并且伪表Inserted 可能包含多行.

Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Inserted might contain multiple rows.

鉴于该表可能包含多行 - 您希望在这里选择哪一行??

Given that that table might contain multiple rows - which one do you expect will be selected here??

SELECT @ITEMMODEL = ITEM_MODEL FROM inserted

未定义 - 您可能会从 Inserted 中的任意行获取值.

It's undefined - you might get the values from arbitrary rows in Inserted.

您需要使用 Inserted WILL 包含多行的知识重写整个触发器！您需要使用基于集合的操作 - 不要期望 Inserted 中只有一行！

You need to rewrite your entire trigger with the knowledge the Inserted WILL contain multiple rows! You need to work with set-based operations - don't expect just a single row in Inserted!

因此，在您的情况下，您的触发器代码应如下所示:

So in your case, your trigger code should look something like this:

CREATE TRIGGER [dbo].[tbl_Sales_ForInsert]
ON [dbo].[SALES] 
FOR INSERT
AS
BEGIN
   -- update the dbo.Sales table, set "PROFIT" to the difference of 
   -- TOTAL_PRICE and (QUANTITY * RATE) from the "Inserted" pseudo table
   UPDATE s
   SET s.PROFIT = i.TOTAL_PRICE - (i.QUANTITY * i.RATE) 
   FROM dbo.Sales s
   INNER JOIN Inserted i ON i.ITEM_MODEL = s.ITEM_MODEL

   -- update the dbo.ITEM_DETAILS table
   UPDATE id
   SET id.QUANTITY = id.QUANTITY - i.Quantity
   FROM dbo.ITEM_DETAILS id
   INNER JOIN Inserted i ON id.ITEM_MODEL = i.ITEM_MODEL
END

相关文章