Trigger firing of another Trigger on a different table

Following the succesfull use of a Running Balance Transaction Trigger like so :

ALTER trigger TRANSACTION_BALANCE
ON dbo.Transactions
FOR UPDATE, INSERT
AS
BEGIN
UPDATE Transactions
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)
END


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 ??

Thanks
show_tAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
Try this:

CREATE trigger ACCOUNT_BALANCE
ON dbo.Accounts
FOR UPDATE
AS
BEGIN
IF UPDATEOF(OpeningBalance)
BEGIN
UPDATE Transactions
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)
END
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
muzzy2003Commented:
... except with UPDATED not UPDATEOF (sorry, not woken up yet) ...
0
OlegPCommented:
CREATE trigger ACCOUNT_BALANCE
ON dbo.Accounts
FOR UPDATE
AS
BEGIN
IF UPDATE(OpeningBalance)
    UPDATE  Transactions
    SET         deposit=deposit
    FROM      Transactions T INNER JOIN INSERTED I
                    ON T.AccID = I.AccID      
END
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

muzzy2003Commented:
OlegP's requires nested triggers to be enabled, but if they are it ought to do the trick as well.
0
muzzy2003Commented:
And in fact, mine will work better without, as if they are, it'll do the updates twice.
0
show_tAuthor Commented:
Thanks all,

but i've never used nested triggers before so muzzy2003's solutions works best for me.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.