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?
The fix is to stop using correlated subqueries and use joins instead.
This is not permitted when the subquery follows =, != , <, <= , >, >= or when the subquery is used as an expression.
Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With