Can @@IDENTITY cause a race condition?

[kempis555]

I don't know if this is the right forum for what's really a programming question, but I was wondering if you do an @@IDENTITY call right after an INSERT it will return the identity created for that INSERT, or might it return the identity of an INSERT performed by a separate process?

-k

 

[tlbroadbent]

@@Identity will return the a value related to the current process but not necessarily the current table. If the table you insert into has triggers, and the triggers insert other records, the value of @@Identity will be the last value inserted not the value inserted into the first table.

SQL BOL says:

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kempis555]

Ooo! That is something to take into consideration.
Thanks for the heads up.

I'm kinda new to SQL programming, so what's the best method for returning a key field of a record you just inserted?

-k