Chart of Accounts

Dear All,

I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently. I am trying to implement an "intelligent" Chart of Accounts for an accounting program. The following is long-winded but please bear with me:

I have a table coa (chart of accounts) with the following schema

CREATE TABLE coa(
coa_id serial not null,
parent_id int not null default 0,
account_name text not null,
amt money default 0,
primary key(coa_id)
);

After populating the database with basic accounts it resembles this (the hierarchy is mine):

coa_id, parent_id, account_name, amt
0, -1, 'Chart of Accounts', 0.00
1, 0, 'Assets', 0.00
5, 1, 'Fixed Assets', 0.00
6, 5, 'Motor Van', 0.00
--truncated ---
2, 0, 'Liabilities', 0.00
3, 0, 'Income', 0.00
4, 0, 'Expenses', 0.00

So far, so good. I would like it so that if the amt of a a child account changes, the parent account is updated, if a child account is deleted, the amount is reduced off of the parent account etc.

I have managed to achieve this using the following trigger functions:

CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS
$body$
begin
update coa set amt = amt - old.amt where coa_id = old.parent_id;
return old;
end;
$body$
LANGUAGE 'plpgsql'

------------------

CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS
$body$
begin
UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id;
return new;
end;
$body$
LANGUAGE 'plpgsql'

------------

CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS
$body$
begin
IF new.parent_id = old.parent_id THEN
UPDATE coa SET amt = amt + (new.amt - old.amt)
WHERE coa_id = new.parent_id;
ELSE
UPDATE coa SET amt = amt - old.amt
WHERE parent_id = old.parent_id;
UPDATE coa SET amt = amt + new.amt
WHERE parent_id = new.parent_id;
END IF;
RETURN new;
end;
$body$
LANGUAGE 'plpgsql'

------------

These have been bound to the respective ROW before triggers. And they work as expected upto a certain extent. eg assigning a value to 'Motor Van' updates the relevant parent accounts:

UPDATE coa SET amt = 4000 WHERE coa_id = 6;

The problem comes about when one wants to change the parent account for a sub account eg, assuming in the example above that 'Motor Van' was a liability, attempting to change its parent_id from 1 to 2 is erronous and somewhat interesting because the amt for all related accounts are reset to unpredictible values, AND the parent_id does not change anyway.

The problem lies squarely in the function coa_upd_amt().

Any ideas.

Thank you.