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))
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.
Your valuable feedback, question, or comments about this article are always welcome.
Nice article,good work
ReplyDelete