disable @@IDENTITY

[dragonwell]

I added an AFTER INSERT trigger to a table. Unfortunately, some application code is using @@IDENTITY to get back the value of the identity column from that table. The trigger does an insert into another table, so @@IDENTITY returns the identity value from that second table and breaks the app. I know we should use SCOPE_IDENTITY(), but is there any way to prevent the insert statement in the trigger from changing the @@IDENTITY value generated by the previous table?

 

[SQLDenis]

>>but is there any way to prevent the insert statement in the trigger from changing the @@IDENTITY value generated by the previous table?


No, not AFAIK, as you said SCOPE_IDENTITY() is the way to go
change all the code to use SCOPE_IDENTITY() instead of @@identity

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

After you fix the code, I would make sure that all developers know that they are never to use @@identity again.

And depending on wheter you found this out after going to production, you may have some bad data integrity problems for anything that fired where it used the wrong identity value and didn't error out. I hope you have audit tables or a good backup to help fix that kind of problem.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

If you absolutely must, you can spoof @@identity if necessary.

Capture the value that the application wants into a variable. At the end of your trigger, insert that value into a temp table. I don't remember it all now, but with some fiddling you should be able to figure out how to spoof it. Pay attention to the primary key of the temp table. You MIGHT need an identity column in it and might need to use SET IDENTITY_INSERT ON.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[AlexCuse]

Interesting E. And a bit terrifying ;-)

For the OP, some words I thought were applicable to his plight:

The answer is always "Yes, we can do it in SQL!"

The right answer is "But, like a size 26 thong, just because you can
does not mean you should!"

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

If you're dealing with a brain-dead application that uses @@identity (such as MS Access) and you absolutely need to insert to another table in a trigger, it's easier to simply spoof @@identity instead of write your own form logic. You can still use a bound form this way and let Access do all the work of data access.

I would avoid the spoofing if at all possible, but if not, do it. I've done it and it works and it's fairly low overhead.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[dragonwell]

Thanks for all your posts on this. We decided to update the application code to use SCOPE_IDENTITY().