|A common problem in writing stored procedures arises when you need to insert one record into table T1 and one or more records into table T2, with the latter containing the PK of the row you just added to T1. Typically, this situation arises when you're doing batch inserts into a parent and child table.|
To handle this situation, SQL Server provides the variable @@Identity. Following the insert into the parent table, you grab the value of its primary key from @@Identity.
This seems straightforward, but there is a subtle issue that can cause problems. Run the following code in Query Analyzer against a test database.
The script creates the table objects, inserts a row into the parent table, saves its key into a variable, then inserts a row into the child table. Now run these commands:
The results will look something like this:
That's a bit surprising. Note the value returned in the second column from identity_problem_b. It's supposed to be 1000, the primary key that was just inserted into the identity_problem_a table. So why is the value 1 rather than 1000?
The reason is that the @@Identity value has no concept of scope in the sense that most programming languages do. Thus, even though the code presented grabs the Identity value immediately after performing the insert into the parent table, the trigger on the parent table performs another insert, into a different table with a different identity seed. So by the time you test @@Identity, its value has already changed and the old value is gone.
To address this problem, SQL Server 2000 adds the Scope_Identity() function, which as its name implies, "remembers" the scope or context. Thus, the identity value inside the trigger is local to the trigger.
To fix the problem, we only need to change one line of the original code:
to this code: