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


Claude said...

This is great! Thanks it saved my job.

lytri said...

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 :)

Vadivel said...

Yep you are right .. I have blogged about this in Jan 2004 itself here