Hi Helen, Lester

I have solved my issue. I have changed the SQL to the following and
removed it from the loop.

INSERT INTO WorkNewitemExtraPricing (WorkRef, ControlRef, SvrRecCnt,
StockPriceTypeRef, SellPriceSys, SellPriceNoTax)
SELECT GEN_ID(Workfiles_Gen, 1), w.ControlRef, wni.SvrRecCnt,
w.StockPriceTypeRef, w.SellPriceSys, w.SellPriceNoTax
FROM WorkNewitemExtraPricing w
JOIN WorkNewItem wni ON (wni.ControlRef = w.ControlRef AND w.SvrRecCnt
IS NULL)
WHERE wni.ControlRef = :ControlRef

This seems to work perfectly. It is fast and does not need to be in a
loop. I also solved the trigger issue. The data I was testing with did
not have the trigger defined (sorry to all involved).

Thanks Helen for questioning the use of SvrReccnt it made me think 'Hey
can I do this using the FK in the other tables and skip the loop'.

I would still like to know what the 'fatal flaw' is, just so I can avoid
it in future :)

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd

>