Trigger Gives an Error on Multiple Rows SQL SERVER 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 =, !=, &lt;, &lt;= , >, >= 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
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