Stop trigger changing scope_identity

Question

My application inserts some data into a table.

insert into this_Table (id, value, value)

I have then created a trigger that does a simple insert, into a different table with a primary key.

insert into temp_wc_triggertest values ('test', GETDATE())

My problem is then, the application tries to look for scope_identity, from the first insert. However, it gets overwritten by the trigger, which changes the scope identity to the primary key of temp_wc_triggertest.

How can I stop the trigger overwriting scope_identity?

I realise there is not much code to help here, which would normally be classed as a bad question, but I don't have permission to access to the full application code at the moment, so I am hoping this is answerable as is.

This is on SQL Server 2008 R2

EDIT: I have looked at the code, and it does use scope_identity

Answer 1

Your client is most certainly using @@IDENTITY instead of SCOPY_IDENTITY()

Here is a SQL Fiddle with some code you can test on.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table T1(ID int identity(1,1));
create table T2(ID int identity(1000, 1));

go

create trigger tr_T1 on T1 for insert
as
insert into T2 default values;

Query:

insert into T1 default values

select @@identity as "@@identity", 
       scope_identity() as "scope_identity()"

Results:

| @@IDENTITY | SCOPE_IDENTITY() |
---------------------------------
|       1000 |                1 |