Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> * Connection 1 starts an insert.
> * While connection 1 is processing, connection 2 initiates an insert as
> well. This one is blocked.
> * When connection 1's insert is finished, connection 2's insert starts.
> * When processing of connection 1 reaches the statement where @@IDENTITY
> is retrieved, the insertion of connection 2 is already finished and the
> @@IDENTITY value returned to connection 1 will be for conenction 2's row.

Sorry Hugo, but this is plain wrong. @@identity is global to the connection,
not global to the server. This cannot happen.

What can happen is that you insert a row into a table and that table has
a trigger which also inserts a row into a table with the IDENTITY property.
In this case @@identity will return the value of the row into the second
table, and you have no good way of getting the value for the first table.

In SQL7 RTM there was also a bug which, if memory serves, caused @@identity
to be NULL, if your trigger inserted into a non-identity table.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp