SCOPE_IDENTITY() fucntion returns the last identity value for any table in current session. It retuns local identity value, NOT across all scopes.
Sample script for creating two tables Table1 ( IDENTITY starts with
1;) and Table2 ( Identity starts with
10 ).
CREATE TABLE Table1 ( ID int identity( 1 ,1), Name varchar(100))
GO
CREATE TABLE Table2 ( ID int identity( 10 , 1), Name VARCHAR(100))
GO
CREATE TRIGGER Tri_InsertTrigger
ON Table1
FOR INSERT
AS
INSERT Table2 SELECT name from inserted
GO
INSERT Table1 VALUES( 'Bandi')
SELECT SCOPE_IDENTITY() ScopeIdentityValue
SELECT ID Table1ID FROM Table1
ScopeIdentityValue
1
Table1ID
1
Even though we have trigger on Table1, the SCOPE_IDENTITY() retuns current scope and local identity value for the table..
NOTE: If you wanted the last identity value of a table across ALL/global scopes, then use the @@IDENTITY property; otherwise use SCOPE_IDENTITY() for local identity value for any of table