View Single Post
  #4 (permalink)  
Old June 9th, 2003, 07:01 AM
Jeff Mason Jeff Mason is offline
Friend of Wrox
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

Note that if you are using SQL Server 2000, you would be somewhat better off using:

SET @newid = scope_identity()

Since the @@IDENTITY function returns the last Identity value used on the connection, if 'TableName' had an INSERT trigger defined (for journaling or audit purposes, say), and that trigger inserted into another table which also had an Identity column, then the results of the @@IDENTITY would be the value of *that* identity insert, and not the one from the original INSERT. The scope_identity() function returns the corrrect value of the identity within the current scope, regardless of how deeply nested triggers or stored procedures are...

Jeff Mason
Custom Apps, Inc.
Reply With Quote