Scope_identity
Returns the last one inserted to the identity column in the same scope.
Identity value. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.
Syntax
Scope_identity ()
Return type
SQL _variant
Note
Scope_identity, ident_current
Similar to @ identity, because they all return
Value in the identity column.
Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the value generated for a specific table in any session and scope. For more information, see ident_current.
Scope_identity and
@ Identity returns the last Identifier value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.
For example, there are two tables T1 and T2, and an insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.
Assume that both T1 and T2 have the identity column. @ identity and scope_identity return different values at the end of the insert Statement on T1.
@ Identity returns the value of the last identity column inserted to any scope in the current session. This value is the value inserted in T2.
Scope_identity () returns the identity value inserted in T1, which is the last insert that occurs in the same scope. If an insert statement occurs in the scope and is called before the identifier column
Scope_identity () function, the function returns a null value.
For more information, see examples.
Example
The following example creates two tables TZ and Ty, and creates an insert trigger on tz. When a row is inserted into the table tz, the trigger (ztrig) is triggered and a row is inserted in ty.
Use tempdb
Go
Create Table TZ (
Z_id int identity (1, 1) primary key,
Z_name varchar (20)
Not null)
Insert TZ
Values ('lisa ')
Insert TZ
Values ('Mike ')
Insert TZ
Values ('carla ')
Select * From TZ
-- Result set: this is how table TZ looks
Z_id z_name
-------------
1 Lisa
2 Mike
3 Carla
Create Table ty (
Y_id
Int identity (100,5) primary key,
Y_name
Varchar (20) null)
Insert ty (y_name)
Values
('Boathouse ')
Insert ty (y_name)
Values ('rocks ')
Insert ty (y_name)
Values
('Evator ')
Select * from Ty
-- Result set: this is how Ty looks:
Y_id y_name
---------------
100 boathouse
105 rocks
110 elevator
/* Create the trigger that inserts a row in Table ty
When a row is inserted in Table TZ */
Create trigger ztrig
On TZ
For insert
Begin
Insert ty values
('')
End
/* Fire the trigger and find out what identity values you
Get
With the @ identity and scope_identity functions */
Insert TZ values ('rosalie ')
Select scope_identity () as [scope_identity]
Go
Select @ identity
As [@ identity]
Go
-- Here is the result set.
Scope_identity
4
/* Scope_identity returned the last identity value in
Same scope, which was the insert on table TZ */
@ Identity
115
/* @ Identity returned the last identity value inserted
Ty by the trigger, which fired due to an earlier insert on TZ */
References: online help documentation