1

I have following tables:

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


tbl_workshop_temp
id int identity
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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Raymond Morphy
  • 2,464
  • 9
  • 51
  • 93
  • I can think of a kludge that might work but You should really change your code. Why is it impossible to change it to call the correct function? – Martin Smith Sep 14 '11 at 12:25
  • Because the code is written with VB6 by the others. And I can change just the database. – Raymond Morphy Sep 14 '11 at 12:31
  • What this SQL Statement is in compiled code that you don't have access to? Really you would need to get this changed but assuming that you are looking for a short term fix I'll post my (horrible) idea. – Martin Smith Sep 14 '11 at 12:36
  • 2
    @Raymond next time don't let "the others" put bad, ad hoc SQL into their applications - encourage / insist on stored procedures! – Aaron Bertrand Sep 14 '11 at 12:52
  • Yes you are right. I'll do it. – Raymond Morphy Sep 14 '11 at 13:04
  • I'm sorry, but this is a case where you must insist the code be fixed by the others. It is broken and will cause data integrity issues where child data is attched to the wrong record. In fact insist that all of the code that uses @@identity in the application be replaced as soon as possible as it is unlikely that incompetent database developers used it in only one place. This is a major bug in their code and they must take responsibility for fixing it. This is a data integrity issue and thus a show-stopper, crtical fix. There can be no excuse for not fixing this code. – HLGEM Sep 14 '11 at 13:19
  • @HLGEM while I agree with you 100% about the optimal course of action, I disagree that this was necessarily due to incompetence. There was a time when `@@IDENTITY` was the prescribed method, and since this app is still running on SQL 2000 I'll hazard a guess that the app was written when that was the "current" version of SQL Server. The code also may be compiled into an application that has no reliable source code, so modifying the app may be more complicated than you would expect. Again, I totally agree that the app should be fixed, just wanted to air those points. :-) – Aaron Bertrand Sep 14 '11 at 17:06
  • Scope_identity was the correct thing to use in SQL Server 2000 as well. – HLGEM Sep 14 '11 at 17:25

3 Answers3

4

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • +1 This will fail if there is any additional code not shown that does multi row inserts however as `id` is presumably intended to be unique in `tbl_workshop_temp` – Martin Smith Sep 14 '11 at 13:07
  • @Martin, yes, that's an important disclaimer... I'm going to add it to the answer... thanks. – Aaron Bertrand Sep 14 '11 at 13:10
  • @Aaron Bertan I changed your code and used this trigger. Am I doing right? `create trigger InsertedWorkshop on tbl_workshop for insert as declare @id int select @id=id from inserted insert into tbl_workshop_temp (id,code,insupkey) select @id,id,1 from inserted` I just want a unique key in my `id` field so I used the `id` of `tbl_workshop`. won't I have any problem when two users insert two records simultaneously? – Raymond Morphy Sep 15 '11 at 03:52
  • In case if table is empty `COALESCE(MAX(id),0) + 1` is better; else you get NULL for id. – JustAMartin Aug 11 '16 at 14:02
  • @JustAMartin yep, was keeping the answer here simple because we can assume in most cases tables like this don't suddenly go from having rows to being completely empty. – Aaron Bertrand Aug 11 '16 at 22:02
1

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.

Community
  • 1
  • 1
Dan Short
  • 9,598
  • 2
  • 28
  • 53
0

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

SELECT ID_FIRST_TABLE FROM TRIGGERED_TABLE WHERE TRIGGERED_ID = @@Identity
Gorgsenegger
  • 7,356
  • 4
  • 51
  • 89
Leonardo
  • 1