You have a process that inserts a record into a table. You also have a trigger that fires after the insert event on the same table. Inside that trigger, you insert a record into a second table. When you attempt to get the identity value assigned from the insert, what value will be returned? The identity value assigned from the insert into the table that fired the trigger, or the identity value assigned from the insert occurring inside the trigger?
Frankly, this is one of the reasons I personally dislike triggers. I use them all the time; especially during transition periods from legacy database schemas to new table layouts. In the triggers, code is hidden “Magically” working, and you often are not even aware of it.
Here is an example of a production problem a developer experienced. The developer was working with a screen that modified the contents of table A. A different developer put a trigger on Table A inserting into Table B, logging the event that Table A had been modified in some way. The trigger worked fine for Updates and Deletes. The problem was with inserts. Both Tables had identity columns.
It turns out that the application was saving table to both a parent table and related child tables. The first insert placed a value into Table A (the parent) and returned the IDENTITY value assigned to the record. Then, each of the child records were inserted with the FK column assigned the results of the returned IDENTITY.
This is a simple scenario, and the code had been working for years. Until the need came to audit the event, and a trigger was introduced.
To cut to the chase, the original developer had used the global SQL Server variable @@IDENTITY to capture the value assigned. This is known to have issues because it returns the last Identity value assigned globally, even outside your own process. Modifying the insert process to use SCOPE_IDENTITY() instead of @@IDENTITY resolved the problem.
The whole processes would have been better served with centralized code where the audit to the second table could have been added without using a trigger. Both developers would have seen the code because it would be in one place. The centralized code would easily reside in the wrapper of an ORM, or inside a stored procedure. No magic code hidden behind the scenes of which others are not aware.
This may be a good time to review your legacy SQL Server code and replace all your @@IDENTITY global variables with the SCOPE_IDENTITY() function. You never know when @@IDENTITY may have crept in, or remained from previous versions of SQL Server not supporting SCOPE_IDENTITY().
Here is a query that will search for @@IDENTITY in a single database using a deprecated view…
SELECT DISTINCT OBJECT_NAME(id)
WHERE text LIKE ‘%@@IDENTITY%’
This query returns all objects, most likely stored procedures, having the text @@IDENTITY in it. This query will not work on stored procedures where the text has been hidden when it was compiled.
You can also use Visual Studio, or even SQL Server Management Studio for that matter, to look for @@IDENTITY where it is embedded in application code. Simply go to the Edit File, Choose Search in Files, key in @@IDENTITY as the search value, and set the path where your code is located. You may have to set the file type as well. This will return a list of all files containing the desired text.
Obviously there are other ways to find the text….grep, find, etc. Use your favorite.
If you would like to share tips you have found useful with our readers, feel free to send them to email@example.com.
Myths and Realities of an Iterative Development Lifecycle (Part 1)
Laura Lee Rose, business coach and corporate exit strategist, questions and debunks some widely held myths pertaining to iterative development and iterative testing. She explains how iterative development principles can address these common misunderstandings and transition to a pragmatic software development methodology.
When you’re looking for a reliable tool to diagnose … (read more)