Hello Rick,

> I'm inserting child records, and if the parent doesn't exist it is
> created. The problem is that the parent has an auto-generated key,
> which I can't see, thus I can't link the child to the parent:
>
> DECLARE VARIABLE PO_ID BIGINT;
> INSERT INTO PURCH_ORDER ( PO, SOME_OTHER_FIELDS )
> VALUES ( NEW.PO, NEW.SOME_OTHER_FIELDS );
> /* required because FB won't return primary key generated by insert */
> /* PO is a candidate key, but not really suitable for a PK */
> SELECT ID FROM PURCH_ORDER WHERE PO=NEW.PO INTO :PO_ID;
> /* create order-item record */
> INSERT INTO ORDER_ITEM (PURCH_ORDER, SOME_MORE_FIELDS)
> VALUES (:PO_ID, SOME_MORE_FIELDS);
>
> The value :PO_ID is NULL, and the last insert fails.

I'll take it this goes into a BEFORE INSERT trigger, right?

My guess is, that the "child" record isn't really in PURCH_ORDER yet,
so, fetching the PO fails.

Why not do:

PO_ID = NEW.PO;

??

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com