Subject Intersection tables and updating the relevant fields
Author mkebruce_uk
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
> 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
> 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
> on BOTH
> tables take care of adding intersection rows when required' but
> there is no
> example of this ?
> I can understand it would be simple enough if I just add records
> 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.