SCOPE_IDENTITY

SCOPE_IDENTITY (T-SQL) 

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

• In simple words SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

For better understanding simply follow below steps and observe results

step 1 :-

create table policy_master (policy_no int identity(1,1),policy_owner varchar(40))

insert into policy_master values ('sandip')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 2 :-

insert into policy_master values ('sandip2')

insert into policy_master values ('sandip3')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 3 :-

insert into policy_master values ('sandip4')

insert into policy_master values ('sandip5')

insert into policy_master values ('sandip6')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 4 :-

create trigger policy_trigger on policy_master

 instead of insert as

 insert into policy_master values ('sandipg')

step 5 :-

 insert into policy_master values ('sandip7')

 select SCOPE_IDENTITY() C_SCOPE_IDENTITY

if you closely observe last step, record is inserted in table policy_master through trigger written on policy_master table itself but scope_identity() function cannot show you last identity of policy master table because insert statement in trigger is out of scope for scope_identity().

                                  

 I hope You got something useful in this article. I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.