This one is one of the basics that all SQL programmers learn at some point... Sometimes the hard way.
@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You could have a situation where there is a trigger that inserts a new record in a Logs Table, for example, when your Stored Procedure or INSERT SQL Statement inserts a record in the Orders Table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the record added into the Log Table and not the Orders Table, which will create a nasty, nasty bug in your data access layer.
To avoid the potential problems associated with someone adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your INSERT SQL Statement or Stored Procedure.
Luckily today when some strange behavior occurred during our QA testing this was the first thing that I thought of, and sure enough, the original developer used @@IDENTITY to get the newly inserted identity value, which at the time was perfectly correct to do; however, we've recently added some trigger functionality, which caused this behavior to produce incorrect results. Switching to SCOPE_IDENTITY() fixed the problem. But this one is definitely one you want to remember if you are using triggers in your code.
0 comments: