Problem

I have two tables with ID column that has IDENTITY property. A trigger on Table1, if I insert data into Table1, the trigger will be fired up to insert data into Table2

Use Demo;
GO
CREATE TABLE Table1(ID int IDENTITY, value varchar(1))
CREATE TABLE Table2(ID int IDENTITY, value varchar(1))
GO
CREATE TRIGGER trg_InsertIntoTable2
ON Table1 AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Table2
SELECT i.value
FROM inserted i
SET NOCOUNT OFF;
END

Inserted a row into Table2

INSERT INTO Table2(value) VALUES(‘A’)

I created a store procedure to insert data into Table1. It returns value of ID column, which inserted into Table1.

CREATE PROCEDURE usp_InsertData
@Value varchar(1)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Table1(value)
VALUES(@Value)

SELECT @@IDENTITY AS ID_Inserted
SET NOCOUNT OFF;
END

 

I executed the store procedure to insert value ‘B’ into Table1 and got the ID which inserted into Table 1

— Data of Table1, Table2 before inserting
SELECT * FROM Table1
GO
EXEC usp_InsertData ‘B’
GO
— Data of Table1, Table2 after inserting
SELECT * FROM Table1
SELECT * FROM Table2

IDENTITY-SCOPE_IDENTITY

I expected to get 1 instead 2 as returned result from my store procedure. What is wrong with my store procedure?

Solutions

@@IDENTITY always returns the last identity value generated in any table in the current session after an INSERT, SELECT INTO or bulk copy statement is completed. It means that @@IDENTITY is limited to the current session but it is not limited to the current scope. If you insert data into a table and it has a trigger that causes an identity to be generated in another table, you will get wrong the value of @@IDENTITY as my above example.

To avoid this, I will change @@IDENTITY to SCOPE_IDENTITY() because SCOPE_IDENTITY() returns the last identity value generated in the current scope in the current session.

I will alter my store procedure to use SCOPE_IDENTITY()

USE Demo;
GO
ALTER PROCEDURE usp_InsertData
@Value varchar(1)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Table1(value)
VALUES(@Value)

SELECT SCOPE_IDENTITY() AS ID_Inserted
SET NOCOUNT OFF;
END

Try to execute the store procedure to insert new value ‘C’ into Table1

— Data of Table1, Table2 before inserting
SELECT * FROM Table1
SELECT * FROM Table2
GO
EXEC usp_InsertData ‘C’
GO
— Data of Table1, Table2 after inserting
SELECT * FROM Table1
SELECT * FROM Table2

IDENTITY-SCOPE_IDENTITY_1

From now, I can get right ID as my expectation while inserting data into Table1

Conclusions

Always be carefully and consider if you wan to use @@IDENTITY variable. In some cases, it will return the unexpected value if your table has triggers or your table is used in replication process…

If you still want to use @@IDENTITY, make sure that there are no processes that cause an identity to be generated in another table in your T-SQL statement or store procedure .

To avoid the potential problems, you should use SCOPE_IDENTITY() instead of using @@IDENTITY. Click here for more detail @@IDENTITY.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s