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
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
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.
