@@IDENTITY vs. SCOPE_IDENTITY()

So, I'm not doing a great job of posting, sorry about that.





This isn't new, but I wanted a proof of concept so I created a quick query to show how using @@IDENTITY won't work if you are inserting into a table that has a trigger on it which inserts into a second table. Instead you want to use SCOPE_IDENTITY()






BEGIN TRAN
CREATE TABLE TempA (ID INT IDENTITY (1, 1), TxtData VARCHAR(55))
CREATE TABLE TempB (ID INT IDENTITY (1000, 1), TAID INT, TextData VARCHAR(55))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TempA_T01]
ON [dbo].[TempA]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO TempB
(
TAID
, TextData
)
SELECT
ID
,'System'
FROM INSERTED
END

INSERT INTO TempA
( TxtData )
VALUES ( 'Test' -- TxtData - varchar(55)
)
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT *
FROM TempA
SELECT *
FROM TempB

ROLLBACK