> This must get asked several times ...
> If you are using an generator for a primary key (PK) in conjunction with an
> Insert Trigger, is there any way with FIB/IBX/TQuery to get back the
> generated PK
> value?
> Or do I have to use a Stored procedure to create an new PK value inside the
> same transaction and then use that in the SQL insert statement?
>
> Are there any other methods of doing this?
> I was just looking for a way to do this without doing 2 SQL statements back
> to the server (ie the SQL select for the stored procedure/generator and the
> SQL
> Insert).
Whilst not in IB (we use ORACLE) this should apply to any DB with SP and
Generator/Sequences.
An object (or aggregate) is managed as 'New' 'Unmodified' or 'modified' via 2
properties
'Modified' and ID.
ID is zero when unsaved or non-zero if saved (modified indicates then whether to
save changes)...
for each aggregate there is a Stored procedure AGGREGATE_INSERT which takes
all the various parameters including ID...
The stored procedure then decides if a new ID is needed (and returns the new value) and
hence whether to use an update statement or insert statement.
Something like
CREATE PROCEDURE SPAM_INSERT(
iID IN OUT NUMBER,
iNAME IN CHAR
)
BEGIN
IF iID=0 THEN
SELECT SPAM_SEQ.NEXTVAL INTO iID FROM DUAL;
INSERT INTO SPAM (ID,NAME) VALUES (iID,iNAME);
ELSE
UPDATE SPAM SET NAME=iNAME WHERE ID=iID;
END IF;
END;
/
This makes for a nice management for Delphi whose objects can be
derived from a parent designed to interact in this style using
procedure Load(DS :TDataset) - // Uses current record of DS to load
procedure Store; // Creates a stored TStoredProc and
Add some BeforeStore and AfterLoad functionality and you're away laughing.
--
Aaron Scott-Boddendijk
Jump Productions
(07) 838-3371 Voice
(07) 838-3372 Fax
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
