Trigger firing of another Trigger on a different table
Posted on 2004-12-01
Following the succesfull use of a Running Balance Transaction Trigger like so :
ALTER trigger TRANSACTION_BALANCE
FOR UPDATE, INSERT
SET balance = OpeningBalance + (select sum(deposit) - sum(payment) FROM Transactions T2 where T2.TID <= T.TID and T2.AccID = T.AccID and T2.void = 0)
FROM Transactions T INNER JOIN dbo.Accounts A on A.AccID = T.AccID
Where T.AccID in (select AccID from inserted)
I am presented with an unexpected problem. The trigger works fine, It gets the current 'OpeningBalance' value from an Accounts table and uses it in the RunningBalance calculations on the Transactions table. The problem arrises when the user changes the OpeningBalance value, which is on the Accounts table. This activity does not fire any triggers on the Transactions table which is what is needed to maintain an accurate running balance in the transactions table.
Currently after the user changes the OpeningBalance value on an account, they would also have to change a value in the transaction table of THAT account in order for the RunningBalance Trigger to fire and maintain accurate Running Balance values.
Any ideas on how to get round this problem ??