Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Identity Values And Triggers
From Wiki
If you use @@IDENTITY and you have a trigger on the table you just inserted a row into which inserts a row into another table you will get back the 'wrong' identity value First create these two tables and insert these four rows
- CREATE TABLE TestOne (id INT IDENTITY,SomeDate DATETIME)
- CREATE TABLE TestTwo (id INT IDENTITY,TestOneID INT,SomeDate DATETIME)
- INSERT TestOne VALUES(GETDATE())
- INSERT TestOne VALUES(GETDATE())
- INSERT TestOne VALUES(GETDATE())
- INSERT TestOne VALUES(GETDATE())
Now create this trigger
- CREATE TRIGGER trTestOne ON [dbo].[TestOne]
- FOR INSERT
- AS
- DECLARE @CreditUserID INT
- SELECT @CreditUserID = (SELECT ID FROM Inserted)
- INSERT TestTwo VALUES(@CreditUserID,GETDATE())
- GO
Let's insert another row into the TestOne table
- INSERT TestOne VALUES(GETDATE())
Now run this
- SELECT @@IDENTITY,SCOPE_IDENTITY()
Now why doesn't @@IDENTITY return 5 but 1? This is because @@IDENTITY doesn't care about scope and returns the last identity value from all the statements, which in this case is from the code within the trigger trTestOne
So the bottom line is this: Always use SCOPE_IDENTITY() unless you DO need the last identity value regradless of scope (for example you need to know the identity from the table insert inside the trigger)
Contributed by: --SQLDenis 03:13, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Pitfalls
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.