Hi, using your help I come up with the following solution for the trigger:

IF (@action = 'INSERT')

BEGIN

-- update existing balances

UPDATE dbo.AccountBalance

SET Balance = Balance + i.Debit - i.Credit

FROM AccountBalance b, INSERTED i

WHERE b.AccountId = i.AccountId;

-- insert new balances

INSERT INTO dbo.AccountBalance (AccountId, Balance )

SELECT ins.AccountId, ins.Debit - ins.Credit AS Balance

FROM INSERTED ins, (

SELECT AccountId, Debit - Credit AS Balance

FROM INSERTED

WHERE AccountId NOT IN (

SELECT b.AccountId FROM dbo.AccountBalance b

INNER JOIN INSERTED i

ON b.AccountId = i.AccountId

)

) n

WHERE ins.AccountId = n.AccountId

END

ELSE IF (@action = 'DELETE')

BEGIN

UPDATE dbo.AccountBalance

SET Balance = Balance - d.Debit + d.Credit

FROM AccountBalance b, DELETED d

WHERE b.AccountId = d.AccountId;

END

ELSE IF (@action = 'UPDATE')

BEGIN

UPDATE dbo.AccountBalance

SET Balance = Balance + i.Debit - d.Debit - i.Credit + d.Credit

FROM AccountBalance b, INSERTED i, DELETED d

WHERE b.AccountId = i.AccountId AND b.AccountId = d.AccountId;

END