Hi,

many2many relation? are you sure?
i think it is a one2many relation.
because parts must have unique part records, but
one part may be so many time in any warehouse.

if so then try this:

insert into stock_on_hand(part_id,Quantity)
select p.id,coalesce(select sum(quantity) from warehouses where
part_id=p.id and is_input=1),0)
from parts p

you need a good management of transaction on
stock_on_hand record lock while you update and commit it from
clients at the same time.

Regards.
Ali

--- In firebird-support@yahoogroups.com, "Myles Wakeham"
<myles@t...> wrote:

>
> Hoping someone can point me in the right direction....
>
> I have a table in Firebird of 'Parts' and a separate table

of 'Warehouses'.

> I need to populate a table of 'Stock_On_Hand' that is basically a

join table

> that resolves the many-to-many relationship of parts and

warehouses.

>
> I would like to create an After Insert trigger in both the Parts

and

> Warehouses table so that when a new Part or Warehouse is created,

it will

> create a corresponding record in the Stock_on_hand table for each
> combination of Parts & Warehouses so that I can store stock on

hand quantity

> data.
>
> That part is ok, and I'm fine with creating the triggers for this.
>
> But I need to create a SQL Script that will take my current data

in Parts &

> Warehouses and create these records for each corresponding row in

these

> tables to initially populate this table.
>
> Can someone steer me in the right direction as to what this SQL

script

> should look like? I believe I should be able to do this with an

INSERT

> statement but I'm pretty new to SQL and would appreciate any

pointers in the

> right direction.
>
> Thanks
> Myles
>
> ===========================
> Myles Wakeham
> Director of Engineering
> Tech Solutions Inc.
> Scottsdale, Arizona USA
> Phone (480) 451-7440
> Web: www.techsol.org
>