[thelist] ASP: @@Identity

Scott Dexter sgd at ti3.com
Fri Nov 10 15:30:41 CST 2000

I use a trigger to insert into a second table instead of using @@Identity:

create Trigger tbGroupWWW_Add_tbGTSAudit
On dbo.tbGroupWWW
For Insert
Set nocount on
	If Update (uid)
			declare @id int
			Select @id=i.uid from inserted i
			Insert into tbGTSAudit(id) values (@id)

(the tbGTSAudit table has defaults on the other columns so the insert is
just the id value)

it grabs the auto-created id and uses it to insert into the second table.
Since @@Identity is a SERVER WIDE global variable, you can't be guaranteed
of its value through the lifetime of your loop. In other words, some other
process can change the @@Identity value without you knowing it.

dunno if this helps your specific scenario, but its an idea at least (you
can use a trigger for inserts, updates, deletes, or combinations thereof)

you would use it to do your Update below ....

work: http://www.ti3.com/
non: http://thinksafely.org/

> -----Original Message-----
> From: Tab Alleman [mailto:talleman at autobex.com]
> Problem: My @@Identity is experiencing a meltdown in the 
> middle of my script
> and I don't know why.

> 			NewID = SELECT @@IDENTITY 'get the 
> autonumber of the new record
> 			UPDATE MainTable SET (fields and 
> values) WHERE ID = NewID

More information about the thelist mailing list