title: SQL Server AFTER INSERT Trigger updating a 2nd table multiple times per insert
body: <p>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.</p>
  
  <p>Example:</p>
  
  <p>Customer Balance is 500</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>Here's the trigger:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Why does the customer table update not stick for each row of the insert?</p>