Click here to Skip to main content
14,977,910 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
What is the difference between scope, identity() and @@identity?

Can you give me an lucid example?

As far as i know that both returns last generated identity column.

doubt regarding @@IDENTITY

scenario:
create table test1
(
id int identity(1,1),
name varchar(50)
)

create table test2
(
id int identity(1,1),
name varchar(50)
)

CREATE TRIGGER Parentinss ON test1 FOR INSERT

AS

BEGIN

insert into test1 values('usa')

END;

CREATE TRIGGER Parentins ON test1 FOR INSERT

AS

BEGIN

insert into test2 values('india')

END;


insert into test1 values('ANURAG')
insert into test1 values('abhishek')
insert into test2 values('japan')
insert into test1 values('china')

select * from test1
select * from test2

select SCOPE_IDENTITY()
select @@IDENTITY

--There are two triggers. Now both triggers is on test1. one trigger inserts into test1 and the other on test2.
--select SCOPE_IDENTITY()
select @@IDENTITY

@@identity gives me the output as 7 . why ?
why doesnt it give me 6.

As both the triggers are there which inserts one in test1 and the other in test2. How it selects 7. that is trigger parentins[trigger] is preferred over parentinns[trigger]. This is the doubt..
Posted
Updated 9-Aug-13 3:18am
v5

   
Comments
anurag19289 8-Aug-13 15:17pm
   
doubt regarding @@IDENTITY

scenario:
create table test1
(
id int identity(1,1),
name varchar(50)
)

create table test2
(
id int identity(1,1),
name varchar(50)
)

CREATE TRIGGER Parentinss ON test1 FOR INSERT

AS

BEGIN

insert into test1 values('usa')

END;

CREATE TRIGGER Parentins ON test1 FOR INSERT

AS

BEGIN

insert into test2 values('india')

END;


insert into test1 values('ANURAG')
insert into test1 values('abhishek')
insert into test2 values('japan')
insert into test1 values('china')

select * from test1
select * from test2

select SCOPE_IDENTITY()
select @@IDENTITY

--There are two triggers. Now both triggers is on test1. one trigger inserts into test1 and the other on test2.
--select SCOPE_IDENTITY()
select @@IDENTITY

@@identity gives me the output as 7 . why ?
why doesnt it give me 6.
Maciej Los 8-Aug-13 16:05pm
   
Insetad posting code here (in comment), use "Improve question" widget.
anurag19289 8-Aug-13 15:19pm
   
As both the triggers are there which inserts one in test1 and the other in test2. How it selects 7. that is trigger parentins[trigger] is preferred over parentinns[trigger]. This is the doubt..
Maciej Los 8-Aug-13 16:08pm
   
+5!
In this case i prefer MSDN ;)
Please, read MSDN documentation:
@@IDENTITY (Transact-SQL)[^]
SCOPE_IDENTITY (Transact-SQL)[^]
There you'll find an answer ;)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900