Scope @@identity in stored procedures

from the Artful SQL Server & Access Tips List

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.

Listing A

DROP TABLE identity_problem_a
DROP TABLE identity_problem_b
DROP TABLE identity_problem_C
CREATE TABLE identity_problem_a        
(        
    identity_problem_a_pk INT IDENTITY(1000,1) PRIMARY KEY,        
    sometext VARCHAR(50) not null              
)
GO

CREATE TABLE identity_problem_b        
(        
    identity_problem_b_pk INT IDENTITY(1,1) PRIMARY KEY,        
    identity_problem_a_pk INT,
    sometext VARCHAR(50) not null              

)
GO

CREATE TABLE identity_problem_c        
(        
    identity_problem_c_pk INT IDENTITY(1,1) PRIMARY KEY,        
    sometext VARCHAR(50) not null              
)
GO

CREATE TRIGGER identity_problem_trI
ON dbo.identity_problem_a
AFTER INSERT
AS
    INSERT INTO identity_problem_c (sometext)
    VALUES ('This text is triggered')
GO
INSERT INTO identity_problem_a ( sometext )        
VALUES ( 'This is some text' )
GO

DECLARE @pk INT
SELECT @pk = @@IDENTITY 
INSERT INTO identity_problem_b ( identity_problem_a_pk, sometext )        
VALUES ( @pk, 'This is still some text' )
GO

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:

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

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:

SELECT @pk = @@IDENTITY

to this code:

SELECT @pk = Scope_Identity()Now the code will perform as intended.

Last updated 22 May 2009

Return to the Artful SQL Server & Access Tips page