子查询返回的值超过1。当子查询后跟=或将子查询用作表达式时，这是不允许的？ [英] Subquery returned more than 1 value. this is not permitted when the subquery follows = or when the subquery is used as an expression?

问题描述

我想在一个表中插入值后更新多个表和值，所以我创建了一个触发器。它适合插入一行，但是一旦我插入更多行，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中是这种情况。取而代之的是，触发器将每条语句触发一次，并且伪表已插入可能包含多行。

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

它是未定义的-您可能会从中获取值已插入中的任意行。

It's undefined - you might get the values from arbitrary rows in 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

这篇关于子查询返回的值超过1。当子查询后跟=或将子查询用作表达式时，这是不允许的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！