Subquery returned more than 1 value. this is not p

问题:

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:

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

Here is my trigger:

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

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?

回答1:

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

回答2:

Marc_s is right about expecting the inserted pseudo table containing more than one row. There are instances that a query might work if a subquery was just limited to one row with a TOP(1).

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