Re: Constraint on an aggregate? (need help writing trigger,

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.