CODEWITHSUNDEEP
sql-servertriggers
IntoNET
IntoNET

Reputation: 504

SQL Server AFTER INSERT Trigger updating a 2nd table multiple times per insert

This has me stumped. We have a transaction table with an AFTER INSERT trigger that updates the balance in a customer table. It's entirely normal that the trigger will fire with multiple rows in the inserted virtual table for a single customer.

Example:

Customer Balance is 500

An INSERT is made on the transaction table that produces multiple rows for the customer, first row is a 50 debit and the second is a 100 debit.

Expected result post trigger is customer balance is 350. Actual result is 450, as it looks like the join in the trigger to customer is returning the same balance each time, rather than the balance after the previous row has updated it.

insert #1 takes the balance of 500 and deducts 50 from it insert #2 takes the balance of 500 not 450 and deducts 100 from it

Here's the trigger:

CREATE TRIGGER [dbo].[tBalances] 
ON  [dbo].[tblCustomerTransaction]
AFTER INSERT
AS 
BEGIN
    IF @@ROWCOUNT = 0 RETURN
    SET NOCOUNT ON; 

    UPDATE  C
    SET     C.AvailableBalance = C.AvailableBalance + CASE WHEN TT.bIsDebit = 1 THEN I.decAmount * -1 ELSE I.decAmount END,
            C.BonusBalance = C.BonusBalance + CASE WHEN TT.bIsDebit = 1 THEN I.decBonusAmount * -1 ELSE I.decBonusAmount END
    FROM    inserted AS I
            INNER JOIN tblCustomer AS C ON I.iCustomerID = C.CustomerId         
            INNER JOIN tblTransactionType AS TT ON I.iTransactionTypeID = TT.iTransactionTypeID         
    WHERE   I.iManagerID = 12345
END

Why does the customer table update not stick for each row of the insert?

Upvotes: 0

Views: 758

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

If you have to compute these aggregates manually (rather than just defining an indexed view and letting SQL Server maintain them automatically), make sure each target row is only subject to a single update - using grouping:

UPDATE  C
SET     C.AvailableBalance = C.AvailableBalance + BalanceChange,
        C.BonusBalance = C.BonusBalance + BonusChange
FROM    (SELECT iCustomerID,
           SUM(CASE WHEN TT.bIsDebit = 1 THEN I.decAmount * -1
                                         ELSE I.decAmount END)
           as BalanceChange,
           SUM(CASE WHEN TT.bIsDebit = 1 THEN I.decBonusAmount * -1
                                         ELSE I.decBonusAmount END)
           as BonusChange
        FROM inserted AS I
        INNER JOIN tblTransactionType AS TT ON I.iTransactionTypeID = TT.iTransactionTypeID
        WHERE   I.iManagerID = 12345
        GROUP BY iCustomerID) t
 INNER JOIN tblCustomer AS C ON t.iCustomerID = C.CustomerId

(If not exactly right, hopefully you can see how this applies to your real query)

Upvotes: 1

Related Questions