Trigger Gives an Error on Multiple Rows SQL SERVER

Question

I have a problem when I create a trigger which is giving me this error:

Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression***

The Trigger is :

Create TRIGGER AccountChange
ON Customer
AFTER UPDATE
AS
BEGIN
    IF(UPDATE(TotalSales))
    BEGIN
        DECLARE @TotalSales MONEY,@AccountNumber NVARCHAR(20),
            @TotalSalesOld MONEY,@customText1 NVARCHAR(50),
            @AccountTypeId int

        SET @TotalSales=(SELECT TotalSales FROM INSERTED)
        SET @TotalSalesOld=(SELECT TotalSales FROM DELETED)
        SET @AccountNumber=(SELECT AccountNumber FROM INSERTED)
        SET @AccountTypeId=(SELECT AccountTypeId FROM INSERTED)


            IF(@TotalSales BETWEEN 0 AND 5000)

                    UPDATE Customer SET AccountTypeID=8 
                      WHERE AccountNumber=@AccountNumber

   END
END

Triggers in SQL Server run once per result set, not once per row in the result set. INSERTED will contain all the rows you have updated in the UPDATE therefore TotalSales could be thousands of different values — Mark Sinkinson
– Mark Sinkinson, Commented Jul 6, 2015 at 10:54

marc_s · Accepted Answer · 2015-07-06 17:24:18Z

Your trigger should look like this to handle UPDATE with multiple affected rows:

CREATE TRIGGER AccountChange
ON dbo.Customer
AFTER UPDATE
AS
BEGIN
    UPDATE dbo.Customer 
    SET AccountTypeID = 8 
    FROM Inserted i
    INNER JOIN Deleted d ON i.AccountId = d.AccountId  -- use the primary key here
    WHERE AccountNumber = i.AccountNumber
      AND i.TotalSales BETWEEN 0 AND 5000
      AND i.TotalSales <> d.TotalSales  -- TotalSales was updated
      AND dbo.Customer.AccountId = i.AccountId   -- use the PK here
END

One single, pretty simple and straightforward set-based statement - and that's all there is!

Paolo · Accepted Answer · 2015-07-06 12:58:48Z

4

since INSERTED can contain more than one row the update could be performed using a join and not fillin single valued variables:

UPDATE C 
SET AccountTypeID = 8 
FROM Customer as C on INSERTED as I on I.AccountNumber = C.AccountNumber
WHERE I.TotalSales between 0 and 5000

answered Jul 6, 2015 at 12:58

Paolo

4344 silver badges12 bronze badges

Add a comment |

Stack Exchange Network

Trigger Gives an Error on Multiple Rows SQL SERVER

2 Answers 2

Your Answer

Hot Network Questions

Trigger Gives an Error on Multiple Rows SQL SERVER

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions