[thelist] Things Found while researching other things - Part 2

Anthony Baratta anthony at baratta.com
Fri Oct 27 10:31:52 CDT 2006


You may know this already - I didn't...
 
<tip author="Anthony Baratta" type="MS SQL, Find Identity Value">
You all know that @@Identity retrieves the last inserted ID for an idenity column, but this variable can be confused by Triggers, and return the last inserted ID for a table touched by the trigger - not the table you are currently working on.

There is an MS SQL function that will give you the current ID for the current table without getting messed up by triggers, its SCOPE_IDENTITY().

e.g. select SCOPE_IDENTITY()

This will always return the last ID inserted for the table you are currently working on (scope) AND within your current session. So if anyone else is working on that same table you will not retreive their ID.

Hope this is helpful.
</tip>



More information about the thelist mailing list