From: | Kenneth Downs <ken(at)secdat(dot)com> |
---|---|
To: | Isak Hansen <isak(dot)hansen(at)gmail(dot)com>, pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Constraint on an aggregate? (need help writing trigger, |
Date: | 2006-07-26 14:59:35 |
Message-ID: | 44C78357.3070108@secdat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Isak Hansen wrote:
> Each entry in 'A' belongs to a single 'business event'. E.g.
> registering a phone bill modifies your accounts payable, phone
> expenses and vat paid accounts. Those transactions better balance out.
>
> There's no 'A' table in the system we base ours on, you'd just have X
> lines with an equal marker field, but it seemed like a good target for
> normalization as each batch of lines had a lot of common data.
>
>
> The journal entries are always balanced.
>
> Ideally we would store the data somewhere else during entry, and only
> let users save their data when they balanced out, but today we save on
> every submit (web app) and use some wonky heuristics to balance them
> out. (not a technical issue..)
>
> Either way, the db should reject any commit which doesn't sum to zero.
A simple way to do this without a lot of tables is as follows:
1) Add a column "closed char(1)" to table A
2) Do not enforce the constraint if closed="N". This allows data entry
of individual lines.
3) Do not allow closed="Y" unless total=0
4) Once closed="Y", disallow all updates (prevents changes to closed batch)
In the "shameless plug" department, our website also has an example of
how to do this with our tool, email me off-list if you want more info on
that.
Attachment | Content-Type | Size |
---|---|---|
ken.vcf | text/x-vcard | 197 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Kris Jurka | 2006-07-26 15:42:56 | Re: Invalid column display size. Cannot be less than zero |
Previous Message | Kenneth Downs | 2006-07-26 14:50:39 | Re: loop with circular updates |