At 11:43 PM 1/11/2005 +0000, you wrote:

>I wish to implement a simple many-to-many relationship and I am not
> > sure of
> > the best way to implement this ? Just three tables A - AB - B so
> > that table
> > A can be a many to B and vice versa with table AB as the
> > intersection table.
> >
> > I understand the concept - but I am not sure of the best way to
> > implement
> > it - for example if I insert 2 records into Table A and at the
>same
> > time 3
> > records into Table B - is there any way of auto-inserting the
> > neccessary
> > records in the Table AB - or do I have to also insert those at the
> > same time
> > from the client end ?
> > Since the transaction is still in progress - I don't see how I can
> > get the
> > values of both inserts into the triggers ? Perhaps I am just being
>a
> > bit
> > stupid - but I have thought about it long and hard ( maybe I can't
> > see the
> > wood for the trees! )
> >
> > I got the idea from the excellent book by Helen Borrie ( The
> > Firebird Book
> > p. 309 ) but although she demonstrates how to creat the tables
> > clearly
> > enough - she simply says ' before insert and before update
>triggers
> > on BOTH
> > tables take care of adding intersection rows when required' but
> > there is no
> > example of this ?

Hmm, yes, that is a bit misleading - reminds me to fix it. I'm rather
spoiled by being an IBO user, which takes care of this sort of thing
painlessly!

> >
> > I can understand it would be simple enough if I just add records
>to
> > table A
> > in one transaction - OR table B - but now how to add records to
> > BOTH tables
> > in the same transaction so that firebird ( I am actually using IB
> > with
> > delphi ) can automatically generate the correct records with a
> > stored proc
> > or whatever directly on the database server ?
> >
> > Thanks in advance for any help anyone can give me on this.

The trick is to use generated surrogate keys for both of the tables in the
intersection, which is where Firebird is really helpful. If you are
inserting to both tables, just pull the PK values over to your application
*before* you post either row to the server. Store them in your app as
variables and, when you're ready, post away, apply them to the columns in
the right order for the actual task you're doing (parent, child,
intersection: the parent-child order depends on the logic of the immediate
task) and you're away.

As soon as you post to a table, the new row is visible to the whole
transaction. The intersection row (which has the foreign keys) is the only
with formal dependencies, which will be satisfied by the time you come to
post the intersection row.

Techniques for pulling the latest generator value are scattered about in
several chapters. The simplest way to get the value is to SELECT
GEN_ID(aGenerator, 1) from RDB$DATABASE and read the result. In a Delphi
dataset, it will be Fields[0].AsInteger.

As for the Before Insert triggers, make sure you write them as recommended
in the book, testing new.ThePK first, and only generating a value if it is
null (or some other value you have earmarked as being temporary).

./heLen