@@identity problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

@@identity problem

I have an interesting issue with @@identity and was wondering if anyone can shed some light on it for me. I have a stored proc that is inserting rows into a table. The insert trigger on the table then saves the value of @@identity to another table. The problem is that it is returning NULL for @@identity! any ideas why this is happening?? (rows are definitely being inserted, and retrieving @@identity is the first thing in the trigger) Cheers,
Ben ‘I reject your reality and substitute my own’ – Adam Savage
Strange, Can you post the SP & Trigger ?
sp is really simple…i wont post the whole thing, but it is something like this: insert into bookamt (col1, col2, col3)
SElect 1, 2, 3
from some_table
where something = somethingElse the first thing the trigger does is call another proc that saves the identity to the other table by passing @@identity. i do not think it is a problem with the code itself- it has been running fine for years and still runs correctly in another copy of the database used by another arm of the business…i am inclined to think it may be more a server or database consistency kind of error!? ‘I reject your reality and substitute my own’ – Adam Savage
Can you check with that the table has only one trigger ? The insert trigger that we are talking about and don’t have any other one ?

also has update and delete triggers, but it is definitely the insert one being fired. ‘I reject your reality and substitute my own’ – Adam Savage
Why don’t you set-up trace and look for SP & Triggers been fired ?

Hi, I think some where in the code you have lost the sequence. ie. @@Identity returns the last identity inserted in the whole database, it may be in any table. Suppose u r inserting into two tables. One has identity say Table1 and other does not say Table2. If u r inserting values into table1 first and then Table2, @@identity will return null if you capture after the insertion into table2 because this table does not have a identity column. What i suggest is instead of @@Identity use IDENT_CURRENT( ‘table_name’ ). this more focused one.

Hm, that reminds me of some old issue way back in SQL Server 7.0:<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B199688>http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B199688</a><br />Anyway, you should resort to use SCOPE_IDENTITY() instead of @@IDENTIY or IDENT_CURRENT. Why? Check out the BOL explanations for IDENT_CURRENT. <br /><br />Btw, nice to see you’re back. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Thanks guys…i guess i will have a look into scope_identity as an alternative. I saw that sql server v7 issue when i was hunting on Google…it is similar, but not quite the same! as a quick workaround to get things up and running again, we just grabbed the max identity value from inserted in the trigger instead of using any of the built in options. Good to be back Frank! I changed jobs about 5 months ago, and have been flat out getting my head around the new systems i am working on and learning C# and ASP.net. Has been a very steep learning curve, but i am slowly getting a bit more confident in the new job. Cheers,
Ben ‘I reject your reality and substitute my own’ – Adam Savage