James,
It is not good practice to delete an account with out first transfering the
amount in that account to another account. You will also need to make sure the
account has a zero balance before deleting it. You will also need to log the
transactions if funds are moved between accounts with a reason why they were
transfred.
To me a "intelegent" accounting system means that when you make an entry in one
account, the system automatically makes a corresponding entry on the other side
of the equal sign. Example credit Office Supplies the system debits Cash On
Hand (or what ever account is used to pay for office supplies).
The issue on the update, try using an if statement like
If new.amt != old.amt Then
Do Amount Changes that you already have in place
End if
The database should then go ahead an update the parent wtihout an issues. If
that does not work create a function that drops the trigger, update the table
and then creates the trigger. I am sure that this type of change (moving
accounts) will not be a common thing once the COA has been set up and in use
for a while.
HTH.
Michael
> Date: Mon, 10 Nov 2008 05:24:03 +0100> From: [EMAIL PROTECTED]> To: [EMAIL
> PROTECTED]> Subject: Re: [GENERAL] Chart of Accounts> CC:
> pgsql-general@postgresql.org> > Hi James,> > There is some my publications
> about SART AML System based on banking> General Ledger (OLAP Data Warehouse
> and Chart of Accounts as dimension> with 60 000+ items) - may be helpful.> >
> http://www.analyticsql.org/documentation.html>
> http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf> > Regards,> Blazej
> Oleszkiewicz> > 2008/10/12 James Hitz <[EMAIL PROTECTED]>:> > 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.> >> >> >> >> > -->
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)> > To
> make changes to your subscription:> >
> http://www.postgresql.org/mailpref/pgsql-general> >> > -- > Sent via
> pgsql-general mailing list (pgsql-general@postgresql.org)> To make changes to
> your subscription:> http://www.postgresql.org/mailpref/pgsql-general
