Subject Re: Basic Firebird SQL question
Author Ali Gökçen

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,coalesce(select sum(quantity) from warehouses where 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.


--- In, "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
> I would like to create an After Insert trigger in both the Parts
> 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
> tables to initially populate this table.
> Can someone steer me in the right direction as to what this SQL
> should look like? I believe I should be able to do this with an
> 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: