Click here to Skip to main content
15,904,288 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

 
Share this answer
 
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 ;)
 
Share this 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