Is @@IDENTITY populated in case of an UPDATE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is @@IDENTITY populated in case of an UPDATE

Author

Topic

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-21 : 03:36:48

Hi Guys...
Is @@IDENTITY populated in case of an UPDATE. I've tried a sample with the below code...but can't get it to work.

I've few programs in production, with this logic, and they swear it is working fine for them...


create table xyz (id int IDENTITY(1,1), x_desc char(10))
insert into xyz values ('abc')  -- repeat 5 times
insert into xyz values ('lmn')  
insert into xyz values ('xyz')  -- repeat 5 times

--now open a new connection to execute this...since @@IDENTITY will hold the previous value from above inserts
declare @id int
set @id = 989898989 -- a junk value
update xyz set x_desc = '123' where x_desc = 'lmn'
set @id = @@identity
select @id as ID_X

select * from xyz

Any advice is greatly appreciated....

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 04:34:42

Absolutely horrible !!!
@@IDENTITY will NOT work for UPDATES !
If they are using code like that and it works, they are just lucky.


declare @id int
update xyz set @id = id ,x_desc = '123' where x_desc = 'lmn'
select @id as ID_X

select * from xyz

set @id = (select id from xyz where x_desc = '123')
update xyz set x_desc = 'lmn' where x_desc = '123'
select @id as ID_X

select * from xyz

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-21 : 04:49:52

thought so..
The code in the SProc is ..

blah..
blah...
UPDATE table....

set @id_var = @@IDENTITY
If NOT (@id_var is NULL)
Begin
    Do other blah...
End

Ideally, the update would always update a record, and the control is also doing the "Do other blah.."

This is part (Execute SQL task 2) of a DTS package. An other "Execute SQL task 1" is run before this task which inserts data into a table with IDENTITY column, using the same connection object.
I'm guessing the @@IDENTITY value from "Execute SQL task 1" is being carried over to "Execute SQL task 2", since it is the same connection.

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 04:50:34

>> I've few programs in production, with this logic, and they swear it is working fine for them
Really? I would check.
Could there be a trigger on the table which inserts into another table to set @@identity and it's that table they want the value from.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-21 : 04:53:32

nope.. no triggers..

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 04:58:04

Sounds like a disaster waiting to happen.
Someone adds some code or another step,
Someone adds a trigger to the table.

I would like to say this is a problem with dts but it's just a problem with your developers (but it's the sort of mess you often see in dts packages).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 06:31:49

Well DTS is one problem,
Bad code another...
Seems like hgorijal has both

!!!

VBProgrammer+DTS = true
/rockmoose

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-21 : 07:34:58

well, Bad Code... yes..
But DTS problem, I'm not sure...
Whe you have two sequential "Execute SQL tasks" using the same connection object, it would be using the same session. And since @@IDENTITY stays for the session, you get the flexibility of using it in multiple tasks. I see this more as feature, than a bug.

...NO, I'm not marketing for Microsoft

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 08:17:50

IMHO anything using @@IDENTITY needs to be changed now that scope-sensitive alternatives now exist

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 09:44:59

quote:
Originally posted by hgorijal
But DTS problem, I'm not sure...
I see this more as feature, than a bug.

Yep, not a bug but it's a problem waiting to happen. Some features it's a good idea not to make use of.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources