Hi All,
I have two tables. The first table (tbl_item) contains an item id and
it's type.
tbl_item
item_id | item_type
--------+----------
A | DIR
B | DIR
C | ASY
D | DIR
E | DIR
F | DIR
G | ASY
The second table (tbl_assembly) contains the components of the
assemblies. It is possible that an assembly is made up of any quantity
of DIR items or one or more assemblies
tbl_assembly
item_id | component_id | quantity
--------+--------------+---------
C | A | 2
C | B | 4
G | C | 3
G | E | 1
G | F | 8
I would like to perform some recursive processing to replace any
assembly used as a component with the appropriate number of components
so that all component_ids are of item_type = 'DIR'.
item_id | component_id | quantity
--------+--------------+---------
C | A | 2
C | B | 4
G | A | 6
G | B | 12
G | E | 1
G | F | 8
I want to perform this processing any time an item_id is INSERTed or
UPDATEDed into tbl_assembly (TRIGGER) and place this result back into
the assembly table.
Any assistance and URL's to documentation or examples is appreciated.
--
Kind Regards,
Keith