Re: Best practices?

A BEFORE trigger is really the way to go here.
The trigger function would check that
the proper number of bars exists or raise
an error.

I believe this will work fine both with autocommit
and block transactions.

The key here is to be sure you enter bars
before foos or you will never get any valid
foos.

Also, note that you will get things out of whack
with the on delete restrict for bar(bar_id).
You make it possible to delete a bar without
accessing foo's trigger, causing a possible
foo with an invalid number of bars. If this
is intentional, that is OK, otherwise an
appropriate trigger on bar will keep things
in sync.

If you meant that *any* transaction included
a SELECT you don't need that if you have the
triggers on both bar and foo. But if you must
have it, you will need a function which returns
SET OF ROWS. In the function, check your
criteria before returning the selected rows.
There are examples of setof rows at
http://www.varlena.com/GeneralBits/Tidbits/

Dohr--
The example here is an aggregate table, foo_bar
where there must be at least m bars for each
foo but not more than n bars for each foo.
I'll let David tell us what foo and bar really
are.

--elein

On Tue, Sep 30, 2003 at 10:03:57AM -0700, David Fetter wrote:
> Kind people,
>
> It's fairly common to have a situation like
>
> "A foo may have one or more bars," as in
>
> CREATE TABLE foo (
> foo_id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE bar (
> bar_id SERIAL PRIMARY KEY
> )
>
> CREATE TABLE foo_bar (
> foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE RESTRICT
> , bar_id INTEGER NOT NULL REFERENCES bar(bar_id) ON DELETE RESTRICT
> );
>
> What I'd like to do is a more general:
>
> For integers 0 <= m <= n <= infinity,
>
> At the end of any transaction involving foo (including INSERT), a foo
> must have between m and n bars.
>
> Is there a standard way to approach this?
>
> TIA for any pointers :)
>
> Cheers,
> D
> --
> David Fetter david(at)fetter(dot)org http://fetter.org/
> phone: +1 510 893 6100 cell: +1 415 235 3778