Hello List,
We are implemententing an accounting system and have two basic tables,
the relevant structure look like:
Table "s78.fintrans"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
id | bigint | not null
postdate | timestamp without time zone |
debit | integer |
credit | integer |
amount | numeric(10,2) |
and:
Table "s78.accountsbal"
Column | Type | Modifiers
-----------+---------------+-----------
glid | bigint | not null
balance | numeric(12,2) | not null
date | date | not null
A typical fintrans record looks like, pretend 10000 is some asset
account and 20000 is some liability account (fintrans holds db/cr
transactions and accountsbal holds balance sheet images):
1 12/05/05 13:45:05 10000 20000 100.00
2 12/06/05 09:55:33 10000 20000 125.00
and accountsbal:
10000 100.00 12/05/05
20000 -100.00 12/05/05
10000 125.00 12/06/05
20000 -125.00 12/06/05
We are wondering if we can implement a trigger that will maintain table
accountsbal so our client application does not have to worry about it.
The trick is that accountsbal needs to be updated for any change in
postdate, debit, credit or amount fields in fintrans table, for example,
changing fintrans record on 12/05/05 to $50 would yield:
10000 50.00 12/05/05
20000 -50.00 12/05/05
10000 75.00 12/06/05
10000 -75.00 12/06/05
Changes are allowed in fintrans.postdate, fintrans.debit,
fintrans.credit and fintrans.amount.
Does anyone have any idea how this can be implemented efficiently using
plpgsql triggers and functions?
Thanks
george