Why am I receiving the following error in a stored procedure that was called from an after insert trigger?
Server: Msg 2627, Level 14, State 1, Procedure usp_ReplicateFoo, line 254
Violation of PRIMARY KEY constraint 'PK_foo'. Cannot insert duplicate key in object 'foo'.
The statement has been terminated.
The purpose of my trigger is that I have to keep certain columns of a table from another database in sync with my table in the current database. In my AFTER INSERT trigger I call a stored procedure that determines what type of change is made on the local table and then calls INSERT or UPDATE procedures as needed. This problem manifests itself when my UPDATE procedure includes a primary key change. It turns out that occasionally there is an existing record in the target table with a matching primary key so I added code to DELETE that record before the UPDATE executes. A SELECT statement in the procedure confirms that the prior record is gone but I still get the PRIMARY KEY constraint violation!
One variation that I've tried is after the DELETE occurs, if the PRIMARY KEY is changing then execute an INSERT followed by a DELETE of the previous record. If no PRIMARY KEY change is occuring then I just do the UPDATE statement which achieves the results that I need.
My question is, why can't I just do the UPDATE after DELETING a conflicting PRIMARY KEY first? This INSERT/DELETE logic seems unnecessary.