@@IDENTITY and SCOPE_IDENTITY() in SQL Server
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.
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.
4 Comments:
Interesting post.. I would counsel the use of the column list in the INSERT statement tho.. If the table has a new column added, your example code would break :)
all well and good but how do you then pass the scope_identity to another table?
Once you've done the above, do another insert into the table
INSERT INTO AnotherTable (id) VALUES (@ReturnValue)
Visit http://technoexperts.blogspot.com/2008/08/sql-server-identity-columns-values.html for details.
Post a Comment