My .NET web application with a SQL Server back end was working fine, until...
I tested a feature that added a new record to one of the database tables, and I got an exception saying that a primary key constraint had been violated.
Using SQL Server Profiler, I was able to isolate the stored procedure call that violated the constraint.
Examining the SQL code in that sproc, I saw something similar to this:
DECLARE @newID INT
INSERT INTO Table1 (Foo, Goo) VALUES ('abc', 'def')
SET @newID = @@IDENTITY
INSERT INTO Table2 (Bar, Car) VALUES (@newID, 'ghi')
The problem was that someone had recently added a trigger on Table1. The trigger fired upon inserting a row into Table1, and executed some code that did an insert into some other table, changing the value of @@IDENTITY. The new value of @@IDENTITY happened to be one that was already in use as the primary key of a row in Table2, violating the constraint.
The fix was simple: use SCOPE_IDENTITY() in place of @@IDENTITY. Thanks to this article for that tip.
