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
could cause you problems. Before I show you how to work around the problem,
let’s look at the problem itself. Run the code in
Listing A in Query Analyzer against some test database.

After creating the objects, you insert a row into the parent
table, and save its key into a variable, and then another row into the child
table. Now run these commands:

select * from identity_problem_a

select * from identity_problem_b

The results will look something like this:

1000    This is some text

1    1    This is still some text

The result is 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 new 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. You need to change this line:

SELECT @pk = @@IDENTITY

to this code:

SELECT @pk = Scope_Identity()

Now the code will perform as intended.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!