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