@@Identity returns the last auto incremented or identity value for any table in the current session over
all scopes. means that Its a GLOBAL identity value...
Example for explaining ALL SCOPES meaning...
Suppose that you have a table called Table1 and one INSERT trigger on Table1. The trigger logic is to insert same record into another table called Table2.
Script for above scenario is as follows:
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
If we insert one record into Table1, then what will be the IDENTITY value now ?
INSERT Table1 VALUES( 'Bandi')
SELECT @@IDENTITY [IdentityValue?]
SELECT ID Table1ID FROM Table1
Output:
IdentityValue?
10
Table1ID
1
It is very clear that the last identity value of Table1 is
1. but @@IDENTITY returned
10 as identity value. means that the 10 is the identity value of last INSERT statement ( i.e. Table2's INSERT statement in TRIGGER)...
@@IDENTITY is global system variable, which returns IDENTITY value across all scopes( here the first table Table1, the trigger Tri_InsertTrigger and Table2).