Re: Identity column

Hi Booth,

Couple ways:
1) Use the IDENTITY_VAL_LOCAL() function which returns the most recently
assigned identity column value.
Ex: VALUES IDENTITY_VAL_LOCAL() INTO :MY_ID_VAR
2) Use the data change table reference FINAL TABLE.
Ex: select NAID# into :MY_ID_VAR from final table ( put your insert
statement here )

I'm guessing option 2) is likely to perform slightly better since it gets
the job done in one request to the database, whereas option 1 takes two
requests. Note: I've never taken the time to benchmark that hypothesis
though.

Note on option 2): you can select additional columns, not just the ID
column, if you wish. If I recall correctly, option 2) will reflect changes
made to the data in BEFORE TRIGGERS, if any are present.

If the table receiving the INSERT has AFTER TRIGGERS present that modify
data in the inserted row, you should use option 1). Typically though,
column value changes in triggers are handled in BEFORE TRIGGERS.

Mike


date: Mon, 30 Nov 2015 21:35:25 -0600

from: Booth Martin <booth@xxxxxxxxxxxx>
subject: Identity column

How do I retrieve a new record that I have just created?

The file is created with a key:
NAID# decimal(5) not null // ID#
generated always as identity
(start with 1),

The scenario is this:
-User keys the new data into blank fields on the screen, but of course
not into the key field.
-User presses Enter key. Data is used to create a new record using
embedded SQL
-User wants to see the record on the screen, with the key field populated.
-I can't figure out how to do that.

--
Booth Martin
www.martinvt.com
(802)461-5349