Re: AW: Identity column

From the same SQL Reference:

Alternative to IDENTITY_VAL_LOCAL:
It is recommended that a SELECT FROM INSERT be used to obtain the
assigned value for an identity column. See “table-reference” on page 660
for more information.

Joep Beckeringh
Pantheon Automatisering

"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>


01-12-2015 07:09

AW: Identity column

Could using option 1 yield an incorrect result if more than one

process is

adding records to the table simultaneously?

IDENTITY_VAL_LOCAL will return the identity value of the most recent

insert

on the same level. An insert in a different job is or activation group

is

performed on a different level. Even a trigger or a different program

or a

procedure activated in the same job/activation group is considered to be

on

a different level.
BTW it also works correctly, if the record gets written with native I/O.
In either way the IDENTITY_VAL_LOCAL function should be executed

immediately

after the insert/write operation for a single row.

From the SQL Reference - Release 7.2:
The value returned is the value that was assigned to the identity column

of

the table identified in the most recent insert operation (specified in
either an INSERT statement or a MERGE statement).
The insert operation has to be issued at the same level; that is, the

value

has to be available locally within the level at which it was assigned

until

replaced by the next assigned value.
A new level is initiated when a trigger, function, or stored procedure

is

invoked. A trigger condition is at the same level as the associated
triggered action.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."

(Les

Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training

them

and keeping them!"