John,

I know you and others have had problems with @@IDENTITY in SQL Server. Here's some possibly useful information I received in an e-mail newsletter from SQL Server Magazine. Hope it helps.


A few astute readers pointed out that Microsoft has documented
problems with using @@Identity. You can find two of those problems by
reading Microsoft Support Online articles Q163446
(http://support.microsoft.com/support/kb/articles/q163/4/46.asp)
and Q199688
(http://support.microsoft.com/support/kb/articles/q199/6/88.asp).
Basically, @@Identity works fine unless you add a trigger to the
original table that inserts a record into another table that doesn't
contain an Identity column. In this case, @@Identity returns Null.
In researching this problem, I found a note in Q199688 claiming this
problem was fixed in SQL Server 7.0 Service Pack 1 (SP1). I chased down
SP1 and found this problem on the fix list in Support Online article
Q225019 (http://support.microsoft.com/support/kb/articles/q225/0/19.asp). For
many applications, installing SP1 resolves to this problem.
You should also be aware that even though you don't have this
problem today, you might in the future. For instance, what if someone
adds or changes a column in a table to an Identity column. Then a few
days later, someone else adds a trigger that inserts a record into
another table. Boom--the problem occurs. If you install SP1 now, you
can forestall the problem in the future because SP1 fixes the errant
behavior in SQL Server 7.0.
The suggestions in the two Microsoft Support Online articles I
mentioned lead you down two different paths to work around the problems
with @@Identity prior to SP1. Both solutions involve creating solutions
that provide unique IDs for each record in a table. You must be careful
when using this approach because someone might be able to go around
your solution and get errant values in the ID column.
If your application is an n-tier solution, consider creating an
Identity object. If you let this object handle the creation of any ID
values, you'll have a manageable solution, but others can still get
around your solution by going directly to the database if security lets
them. This solution works best when you are creating complex nonnumeric
IDs.