Thanks for your help Adam, I did think it could be done with SP - but after reading Helen Borrie's book and it said that you should do it with triggers I thought its a more elegant solution - but then when I REALLY thought about it - I realised that it can be done ( by me anyway :) ) - perhaps Helen knows how to do it.
Regards,
Mike Bruce

----- Original Message -----
From: Adam
To: firebird-support@yahoogroups.com
Sent: Wednesday, November 02, 2005 12:53 AM
Subject: [firebird-support] Re: Intersection tables and updating the relevant fields




Step 1: insert into A .....;
Step 2: insert into B .....;

(Those two steps can be reversed without issue)

Step 3: insert into AB .....;

You could create a stored procedure to create an A and B and AB records. Which records in A and B do you want to match into AB? Unless there is some way of telling which B records to link in an insert trigger on A, then you can not use triggers.

But you can always use a SP approach.

create procedure MakeABPair
(
AField1 integer
AField2 integer
AField3 integer
BField1 integer
BField2 integer
BField3 integer
)
returning
(
aid integer,
bid integer
)
as
begin
aid = gen_id(aid,1);
bid = gen_id(bid,1)
insert into a (id, f1, f2, f3) values (:aid, :AField1, :AField2, :AField3);
insert into b (id, f1, f2, f3 )values (:bid,:BField1, :AField2, :AField3);
insert into ab (aid, bid) values (:aid,:bid);
suspend; -- if using select syntax, remove if using execute procedure syntax
end
^

Then you can simply run the stored procedure to do the linking automatically.




--- In firebird-support@yahoogroups.com, "mkebruce_uk" <michael.bruce7@b...> 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 ?
> >
> > 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.
>

--- In firebird-support@yahoogroups.com, "mkebruce_uk" <michael.bruce7@b...> 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 ?
> >
> > 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.
>




[Non-text portions of this message have been removed]



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




------------------------------------------------------------------------------
YAHOO! GROUPS LINKS

a.. Visit your group "firebird-support" on the web.

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


------------------------------------------------------------------------------



[Non-text portions of this message have been removed]