?
Solved

Running Balance Trigger Update

Posted on 2004-11-11
3
Medium Priority
?
300 Views
Last Modified: 2012-05-05
Hello,

With this Trigger :
ALTER trigger TRANSACTION_BALANCE
ON dbo.Transactions
FOR UPDATE, INSERT
AS
DECLARE @AccBalance money

BEGIN
UPDATE Transactions
      SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 where t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID and t2.void = 0)
      FROM INSERTED i
      WHERE Transactions.AccID = i.AccID
      AND Transactions.TID >= i.TID
END


I'm trying to capture the last 'balance' value updated by the trigger, store it in a local variable, then insert that value in another table.  

I'm not sure how/whether to integrate these require within this trigger or to write another to handle that.  
this is what i need to do.

DECLARE @AccBalance money
INSERT Accounts(Balance, LastUpdated) values (@AccBalance, GETDATE())
WHERE Transactions.AccID = i.AccID

?


Thank-you
0
Comment
Question by:show_t
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
ShogunWade earned 450 total points
ID: 12553203
It may be better to simply have a view over the Accounts & Transactions Table.  
0
 

Author Comment

by:show_t
ID: 12553671
I am aware of this, i'm currently using a sproc to update my Accounts table for the afore mentioned task.
But i'm trying to improve db performance and cut down on code.  

If i used a trigger, this would be so,

Thanks
0
 

Author Comment

by:show_t
ID: 12553681
Plus i try and use triggers more these days as i'm not very good at them...
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

809 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