Does @@identity return the primary key or identity field?

Question

Hi,

My issue is that I've got update triggers on an SQL View (MS SQL 2005) which I'm mapping to LINQ to SQL entities in C#...

My SQL looks correct but it complains about trying to insert a null value into a secondary table PK field.

I believe my issue relates to having the primary key and identity as separate fields in the primary table. So my question is this....when using @@identity, does it look at the primary key of the inserted row, or does it look at the field with "IDENTITY" specified???

Answer 1

@@IDENTITY only returns the identity value from the last insert. It has no idea whether that value was used in a primary key column or even if it is going to be unique for the given column. Rather than using @@IDENTITY, you should use SCOPE_IDENTITY() especially if you have triggers. @@IDENTITY only cares about the column (there can only be one on a table) that has the IDENTITY attribute. Whether the table has a primary key or not and whether the PK is the identity column makes no difference.

See SCOPE_IDENTITY for more.

Answer 2

You want to use SELECT SCOPE_IDENTITY() instead of @@Identity

Answer 3

@@IDENTITY always references the identity column, never at a table's primary key. (How would that work if it were a compound primary key?) As Thomas says, since you are working with triggers, you should probably be using SCOPE_IDENTITY().