• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

Running Balance Trigger

Hello,

I have a table called Transactions like so :

TID AccID Date           Payment   Deposit Type Balance Void LastUpdated
3    238    01/11/2004 0             10         D      0          0      09/08/2004 09:33:42
4    238    01/11/2004 30            0          P      0          0      09/08/2004 09:33:42

I'm trying to write a Trigger so that when either the payment or deposit fields
are updated, the trigger will automatically update the balance fields accordingly.  

OpeningBalance = 0
so (Balance) for TID=3  should say 10 but for (balance) for TID = 4 should say
-20


Thanks
0
show_t
Asked:
show_t
  • 7
  • 4
1 Solution
 
lengreenCommented:
Will be something like this (not tested so maybe a typo somewhere)

CREATE TRIGGER myTrigger
ON dbo.Transactions
FOR INSERT
AS

declare @old money
declare @new money
select  top 1 @old =Transactions.Balance from Transactions  inner join  inserted on    inserted.TID =Transactions.TID where Transactions.TID < inserted.TID order by Transactions.TID Desc

select  @new = Payment  + Deposit from inserted


update Transactions set Balance = isnull(@new + @old,0) from Balance inner join  inserted on  inserted.[TID] =Transactions.[TID]


cheers

Len
0
 
BillAn1Commented:
something like this will do the trick. You will need to update not only the record that has been updated, but any sunsequent records (e.g. in your example, if TID 3 is altered, the balance for TID 4 also needs to be updated, hence the join to inserted


create trigger trans_trig on Transactions
for update, insert
as
begin
      update Transactions
      set balance = (
            select sum(deposit) - sum(payment)
            from Transactions t2
            where t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID
      )
      from inserted i
      where Transactions.AccID = i.AccID
      and transactions.TID >= i.TID
end
0
 
show_tAuthor Commented:
Hello BillAn1 ,

Your Trigger is working except every other time i test the trigger i get an error message saying :

Key column information is insufficient or incorrect, Too many rows were affected by update

Any ideas why ?

Thanks
:-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
BillAn1Commented:
That error is normally associated with trying to do a delete using enterprise manager. Is this what you were trying to do?
If so, it is normally because you have duplicate rows of data, and so EM does not know which row to delete etc.
Can you give some detail of what operation you were trying to do whe you got the error - e.g. delete, insert or update, *and what you were using - EM, Query Analyser etc)
Also, do you have any primary keys / unique constraints on your table?
And finally, this trigger will not work correctly on deletes. If you expect to delete rows, and need to update the balance in existing rows automatically you will need to create a FOR DELETE trigger also. :

create trigger trans_trig_delete on Transactions
for delete
as
begin
     update Transactions
     set balance = (
          select sum(deposit) - sum(payment)
          from Transactions t2
          where t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID
     )
     from deleted d
     where Transactions.AccID = d.AccID
     and transactions.TID >= d.TID
end
0
 
show_tAuthor Commented:
Hello,
and thanks for your help.  Previous problem seems to have stopped.  It seems i only get that error when i directly edit data in the table itself.   When i edit data through my form and sprocs, triggers workd fine.  

Could you please explain the trigger to me in laymans terms ?

Especially this bit :

from Transactions t2
          where t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID
     )
     from inserted i
     where Transactions.AccID = i.AccID
     and transactions.TID >= i.TID


I need to add one more constraint to the trigger and don't know how? i need to add where 'Void=0'.  


Thanks..
0
 
show_tAuthor Commented:
Also,

how come the trigger loops through all the records ??
i just don't get that !

Thanks again
0
 
show_tAuthor Commented:
Sorry,

forget last comment about loops, i get that !
Unfortunately i have an additional problem, whenever i INSERT a record i get the error message:

"-2147467259 Key column information is insufficient or incorrect.  Too many rows were affected by update"

Thanks
0
 
BillAn1Commented:
   update Transactions
     set balance = (
          select sum(deposit) - sum(payment)
          from Transactions t2
          where t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID
     )
     from inserted i
     where Transactions.AccID = i.AccID
     and transactions.TID >= i.TID
end

this is updating Transactions, setting the balance to a comuptedvalue, for each row in Transactions that has a corresponding row in the inserted table with the same Account ID, and a TID >= the one which was inserted.
The inner query is the one that actually calculates the balance. For each record in the target table, it scans another copy of Transactions (t2) and finds all the records which have the same AccID as the targer record (t2.AccID = Transactions.AccID) , and which have a lower TID (t2.TID <= Transactions.TID). These are all the transactions for this acount, up to and including the current transaction. The balance will then be the sum of the deposits made up to this point, less the sum of payments made -
sum(deposit) - sum(payment)


Presumably, when you say 'void = 0' you mean that in calculating the balance, we shoudl only include tose records where void = 0?

then you want
create trigger trans_trig on Transactions
for update, insert
as
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


as to your error, that error is normally caused only from within Enterprise Manager when you try to update a record, and EM cannot distingush which record(s) to update - e.g. you do not have a primary key etc.
Can you insert rows etc from e.g. Query Analyser?
Do you have a primary key on your table?
0
 
show_tAuthor Commented:
Excellent, that was perfect !

Thanks a lot again !

Sprocs are great, but Triggers is something i have been trying to get to grips with for ages !  Sometimes Its like
there is a hidden art to them !  
:-)
0
 
show_tAuthor Commented:
Ahh,  

i have a little problem with your trigger, It doesn't seem to work well when there are 3 or less records in the table.  

Any ideas ?
It simply returns NULLs for the balance values in this circumstance.

Thanks
0
 
show_tAuthor Commented:
Forget last post,

I believe TRIGGER is working fine, its just that there are several NULL values in the balance column which will be
really confusing to the user.  

I don't understand how comes NULLs, but it still seems to get the right answer in the end ?

Thanks
0
 
BillAn1Commented:
The balance will be set to NULL if the inner query returns nothing, i.e. if htere are no records matching the criteria for the inner query. This is presumably if the void is <> 0 for all records up to and including the transaction in question?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now