trigger: inserting to a table from this *and* another

[markSaunders]

i have tbl_contacts and tbl_locations.

When i create a contact i want the trigger? to create a new entry in the link table tbl_con_loc that contains the contact ID (from tbl_contacts) and the location IDs for each location that exists (from tbl_locations)

an example of this would be

location
1 - locA
2 - locB
3 - locC

inserting client 29 would thus result in
tbl_con_loc
1 - 29,1
2 - 29,2
3 - 29,3

i will put in other fields etc - but this give the general intention.

i have realise i need to use the inserted table to ascertaing the contact id that has just been created but how do i loop the locations table for each entry their?

any help would be greatly appreciated!

cheers
mark
Mark Saunders

 

[fluteplr]

You don't.

But you can do this

create trigger tr_tbl_contacts_insert
as
insert into tbl_con_loc (myfield1, myfield2)
select contact_id, location from tbllocations, inserted

This will create a crossjoin between inserted and tbllocations adding a record for each entry in locations

 

[markSaunders]

excellent stuff - have now used variants of this for all my inserts to linked tables and also the deletes - much easier than i had anticipated -

thanks! Mark Saunders