Thursday, September 15, 2005

@@IDENTITY returns wrong identity field

When using @@IDENTITY I get the wrong results
I have seen this problem posted many times on newsgroups and It actually happened to a co-worker not too long ago
This ‘problem’ can occur when you do an insert into Table A and there is at trigger defined that fires when the insert happens and this trigger does an insert into table B.
What @@IDENTITY returns is actually the identity from Table B
In order to get the identity back from table A you should use SCOPE_IDENTITY() instead
at

3 comments:

  1. Claude1:02 PM

    This is great! Thanks it saved my job.

    ReplyDelete
  2. lytri11:13 AM

    I wouldn't say it's the wrong identity field. Just not the one you expected. It works exactly as it's supposed to, just not the way we all want it to :)

    ReplyDelete
  3. Vadivel4:03 AM

    Yep you are right .. I have blogged about this in Jan 2004 itself here
    http://vadivel.blogspot.com/2004/01/fetching-identity-value.html

    ReplyDelete