using @@identity when a trigger is fired in response to an INSERT, and the trigger executes another INSERT statement

I have following tables:

tbl_workshop
id  int  identity
Name nvarchar(10)
Address nvarchar(40)


tbl_workshop_temp
id int identity开发者_JAVA百科
code int
InsUpkey  smallint

And I have follwing statements

insert into tbl_workshop
    (Name,Address)
     values('x','y')
select @@identity  -- My problem is here

And I have following trigger for insert too:

create trigger InsertedWorkshop
on tbl_workshop
for insert
as
  insert into tbl_workshop_temp
     (code,InsUpKey)
    select id,1
        from inserted

when the select @@identity statement runs I get the id of inserted row in tbl_workshop_temp instead of id inserted in tbl_workshop

I know I must use scope_identity but I can't change the code. I just can change my trigger. What should I do now? Please help me.

You can't trick @@identity to return some different value. What you could do is remove the IDENTITY property from the tbl_workshop_temp and either not have an incrementing value in that column (just use the same id as tbl_workshop), remove the column altogether, or populate it in the trigger this way (this means that it will only work for one row, however):

DECLARE @id INT;

SELECT @id = MAX(id) + 1
   FROM dbo.tbl_workshop_temp WITH (UPDLOCK, HOLDLOCK); -- important

insert into dbo.tbl_workshop_temp
     (id,code,InsUpKey)
    select @id,id,1
        from inserted;

With SQL Server 2005 and above you could get more clever - you could change the above such that it used ROW_NUMBER() to handle multiple rows, or you could use an INSTEAD OF TRIGGER instead (no pun intended) and simply change the order of the insert statements. But with SQL Server 2000 your options really are limited...

See this question for how each of the identity selections work. You're going to have to change the the code for the initial INSERT in order to fix this one. @@identity always gets the last id from the session, and since the trigger is part of the same session, you'll have to change the way the ID is picked up in the application.

You can select in the inserted row with where clause like this

SELECT ID_FIRST_TABLE FROM TRIGGERED_TABLE WHERE TRIGGERED_ID = @@Identity