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.
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 >
