Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trigger firing of another Trigger on a different table

Posted on 2004-12-01
6
Medium Priority
?
324 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:show_t
  • 4
6 Comments
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 700 total points
ID: 12713983
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12713986
... except with UPDATED not UPDATEOF (sorry, not woken up yet) ...
0
 
LVL 6

Expert Comment

by:OlegP
ID: 12714109
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 16

Expert Comment

by:muzzy2003
ID: 12714131
OlegP's requires nested triggers to be enabled, but if they are it ought to do the trick as well.
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12714135
And in fact, mine will work better without, as if they are, it'll do the updates twice.
0
 

Author Comment

by:show_t
ID: 12714153
Thanks all,

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

Thanks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question