sql7 @@identity and triggers

How do I get the identity for a row I just inserted? When using @@identity and there are triggers inserting rows into other tables I get the wrong id.

I know sql2000 has scope_identity but I'm stuck with sql7 for legacy reasons.

[232 byte] By [PaulKnepper] at [2008-3-7]
# 1
SCOPE_IDENTITY(). Sometimes if/when returning this to your DAL make sure you cast to an int(4)

MarcD at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2
The trick is to use identity() funtion and select/into.
Here is a quick demo:
create table t1(i int identity)
create table t2(j int identity(10,1), i int)
go
create trigger _t1 on t1
for insert
as
declare @saveident varchar(8)
select @saveident=cast(@@identity as varchar)
insert t2
select * from inserted
exec ('select i=identity(int,'+@saveident+',1) into #tmp')
go
insert t1 default values
select @@identity [t1_ident]
go
drop table t2,t1
go
oj at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3
Whewps, that would be my bad. I did not fullly read what the OP said.
MarcD at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server

Site Classified