Tuesday, 8 March 2016

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.


1 comment:

8451831077