Most of us application developers have worked with SQL Server databases and especially tables with IDENTITY property. We have all written stored procedures to insert records into tables with columns that have IDENTITY property set.

In case you are not familiar with IDENTITY property, this property can be set on a column that has a data type of decimal, int, numeric, smallint, bigint, or tinyint. When this property is set SQL Server automatically populates that column with a next higher number in the sequence when a record is inserted. So it has been somewhat of a common practice for application developers to set the IDENTITY property for an Id column of a table and let SQL Server manage the unique primary key values. More information about IDENTITY property can be found here

Normally one of the requirement when you write a stored procedure to insert a record into a table with IDENTITY column is that you will have to pass the Id value of the newly created record back to the application. Lot of developers use the @@identity variable in SQL Server to do that. And most of the time this will work because the @@identity variable holds the last identity value generated by the insert statement. Run the sql1.sql file from the attached zip file to see this working.

So far so good. Now all that is needed for this nicely working proc to fail to return the correct Id value is to add a trigger to our table, and that trigger inserts a record into another table (say an auditing table) which also happens to have a column that has its IDENTITY property set. Run the sql2.sql file from the attached zip file to see this behavior.

So why did the incorrect id values are returned after adding the trigger. The reason is that @@identity does not have a concept of scope. It always returns the identity value of last inserted record in the database. In this case it happens to the audit table record that got inserted last. So to overcome this behavior we need to modify the proc to use the scope_identity() function to return the last identity value instead of the @@identity variable. Run the sql3.sql file from the attached zip file to see the modified proc and the correct behavior.

So always use @scope_identity() function in your insert proc to return identity column values even though you may not have triggers on your table during development. Because adding a trigger on your table is beyond your control. All it takes is for your proc and application to not work properly or even worse cause data corruption is for someone else to add a trigger on one of your tables for some reason or the other.