If you’re using SQL Server, and you’re inserting records into a table with an auto increment primary key, I suggest you don’t use @@IDENTITY to retrieve the previously inserted Id. Rather use SCOPE_IDENTITY().

The reason for this is that @@IDENTITY gives you the last generated Id in the current session vs. SCOPE_IDENTITY() gives you the last generated Id in the current scope.

The current session includes any triggers that run on the table after the insert. The current scope is just the current stored procedure that you’re in.

We were bitten by this recently. We were using @@IDENTITY when inserted into a table that is replicated. Upon insert the replication trigger was firing, inserting a record somewhere else. @@IDENTITY was giving us the identity of the record the trigger inserted.

Changing the @@IDENTITY to SCOPE_IDENTITY() fixed the problem.

This was even nastier in that by total fluke, the identity was matching a record that already existed, so when we used the retrieved identity to insert into another table, we didn’t violate the foreign key constraint.

Lesson learnt!

Update: I’ve found a page with an in-depth look at the different techniques. Its for SQL Server 2000, but applies to 2005/2008 too.