troubleshooting Question

Primary key violation during UPDATE statement

Avatar of jeffr
jeffr asked on
Microsoft SQL Server
9 Comments1 Solution702 ViewsLast Modified:
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.

Thanks,
jeff
ASKER CERTIFIED SOLUTION
Vadim Rapp

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros