I've just been told about @@IDENTITY, and how it won't always do as expected. When running a stored procedure that does something like the following:
INSERT INTO TEST VALUES( 1, 2, 3, 4, 5)
SELECT @ReturnValue = @@IDENTITY
the @@IDENTITY returns the value of the new row's identity field. If there's a trigger on the table that inserts data into another table, then @@IDENTITY will contain the ID from that table, not the one expected. This is because @@IDENTITY is global. SCOPE_IDENTITY() is local, so the following will return the same value whether there is an insert trigger or not:
INSERT INTO TEST VALUES( 1, 2, 3, 4, 5)
SELECT @ReturnValue = SCOPE_IDENTITY()
Therefore, one should use SCOPE_IDENTITY() instead of @@IDENTITY.
