Geek asks and answers

Subquery returned more than 1 value. is this unacceptable when a subquery follows = or when a subquery is used as an expression?

I want to update several tables and values ​​after pasting the values ​​into one table in order to create a trigger. It works great for inserting one row, but as soon as I insert more rows, SQL Server gives me the following error:

Subquery

returns more than 1 value. is this unacceptable when a subquery follows = or when a 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

The SALESfirst time I insert data into a table , the update was successful, but the second time it causes the error above. ITEM_MODEL- foreign key constraint in the SALES table.

I suffer from this error, can someone help me?

+3
2

, , , , - SQL Server. , - Inserted .

, , ,

SELECT @ITEMMODEL = ITEM_MODEL FROM inserted

undefined - Inserted.

Inserted WILL, ! - Inserted!

, :

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
+7

Marc_s , , - . , , 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
0

More articles:


All Articles