asked
Calulating and updating fields after every insert
Hi All, I need to calculate a Running Total and update fields in table after a new record is inserted. I have following table: Transaction id int, AccountId int, DrAmt money, CrAmt money, TotDrAmt money, TotCrAmt money, FinalBalance money Values for fields AccountId, DrAmt and CrAmt are inserted from my front-end application. After every new record is inserted I would like TotDrAmt, TotCrAmt and Finalbalance fields to be update via trigger or a function. Can someone please help?
Not really enough information to provide you with a full T-SQL statement. What have you tried that's not working?
What version of SQL Server are you running? If it's 2012+ you could look at the LAG and LEAD windowing functions for a running total. If it's earlier, take a look at this
http://www.sqlservercentral.com/articles/T-SQL/68467/ (I'll probably get flamed by some people for suggesting it).
Here goes a trigger assuming SQL2008. CREATE TRIGGER [dbo].[Tr_Sumtotals] ON [Transaction] AFTER INSERT AS BEGIN with cte_tr As( select
ee.id, o.totD,o.totC, ee.TotDrAmt, ee.TotCrAmt ,ee.FinalBalance from [Transaction] ee left join (Select
i.id, sum (i.DrAmt) Over (partition by i.accountid order by
i.id asc) totD , sum (i.CrAmt) Over (partition by i.accountid order by
i.id asc) totC from [Transaction] i ) o on
ee.id=
o.id Where Exists (Select * From inserted s Where
s.id=
ee.id ) ) update cte_tr set cte_tr.TotDrAmt = totD,cte_tr.TotCrAmt = totC, cte_tr.FinalBalance = totC-totD END
17 People are following this question.
