My bad, I'm an idiot. I disabled a generator trigger and forgot to
reenable it. Sorry for the spam.

-----Original Message-----
From: Rick DeBay
Sent: Thursday, December 23, 2004 11:30 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Can't see generated PK within transaction


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.

Rick DeBay
Senior Software Developer
RxStrategies.net




Yahoo! Groups Links