Обсуждение: Constraint on an aggregate? (need help writing trigger, i think..)

I have the following two tables:

create table a (
  id serial primary key,
);
create table b (
  id serial primary key,
  a_id int4 references a (id),
  amount decimal(16, 2)
);

and would like a constraint to guarantee that "sum(b.amount) = 0 group
by b.a_id".


From my testing so far, and this thread:

<http://groups.google.com/group/comp.databases/browse_thread/thread/c2ce2d61eecf5c6c/1e0fa71282aea7d8#1e0fa71282aea7d8>,
i think a trigger is the way to go.

Problem is, i have no idea where to go from here. Getting the model
nearly to to 3NF and writing some simple queries is about the extent
of my db-related skillset..

Anyone feel the calling..? ;)


Also, how would this kind of check affect performance? Table 'b' is
our ledger table, busiest one in the app i assume, while 'a' groups
related transactions and holds common info. We inherited the term
voucher for 'a', anyone know if that is(n't) appropriate?

If someone are interested, the actual tables are here:
a: http://trac.lodo.no/wiki/vouchers
b: http://trac.lodo.no/wiki/voucher_lines


Any feedback appreciated,
Isak

Isak Hansen wrote:

Hello Isak!  I was speaking to you about this on comp.databases, glad to
see you here on the Postgres group.

What you want to do is easy enough in the single case, but can get
complicated if you do a lot of it.

<shameless plug>
We have a framework that is freely available that does exactly what you
are trying to do, and works against PostgreSQL and is written in PHP.
It writes all of the triggers for you to save the hassle and prevent
mistakes. We even have on our website an example of the kind of
constraint you are doing:

http://docs.secdat.com/index.php?gp_page=x_docview&gppn=Customer+Credit+Limit
</shamelss plug>

If you wish to code this by hand, here is what you must do:

1)  Add column "amount" to table A
2)  Add insert, update, and delete triggers to B that increase and
decrease the value of A.amount
3)  Add an update trigger to A (insert and delete not necessary) that
enforces your constraint, or just do it as a check constraint (i
personally prefer triggers)

BTW, is table A supposed to be a GL batch summary table or something?
Why must it always be zero?  If it is a GL batch table, can it be out of
balance while people are actually entering the data?  Should the
constraint only be enforced when the batch is closed?

>
> create table a (
>  id serial primary key,
> );
> create table b (
>  id serial primary key,
>  a_id int4 references a (id),
>  amount decimal(16, 2)
> );
>
> and would like a constraint to guarantee that "sum(b.amount) = 0 group
> by b.a_id".
>
>
> From my testing so far, and this thread:
>
<http://groups.google.com/group/comp.databases/browse_thread/thread/c2ce2d61eecf5c6c/1e0fa71282aea7d8#1e0fa71282aea7d8>,

>
> i think a trigger is the way to go.
>
> Problem is, i have no idea where to go from here. Getting the model
> nearly to to 3NF and writing some simple queries is about the extent
> of my db-related skillset..
>
> Anyone feel the calling..? ;)
>
>
> Also, how would this kind of check affect performance? Table 'b' is
> our ledger table, busiest one in the app i assume, while 'a' groups
> related transactions and holds common info. We inherited the term
> voucher for 'a', anyone know if that is(n't) appropriate?
>
> If someone are interested, the actual tables are here:
> a: http://trac.lodo.no/wiki/vouchers
> b: http://trac.lodo.no/wiki/voucher_lines
>
>
> Any feedback appreciated,
> Isak
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

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.