CODEWITHSUNDEEP
sqlsql-server
Francesco
Francesco

Reputation: 10860

Duplicate key exception when updating (not inserting) a field in a DB table

I created a trigger for a table (Person) so that each time a new Person entry is created its ID is inserted in another table (Person_ID). Person_ID table has only 2 rows: ID (primary key, int and Identity) and Person_ID (GUID whose values is passed with the trigger). This schema cannot be changed due to other dependencies with our business logic.

Now I need to update a field of Person table with the Person_ID.ID (the identity automatically generated once that person has been created). To do that I created a trigger for Person_ID so that once a new entry is created, the generated ID will updated the target field in Person table:

UPDATE Person
SET target_Person = (SELECT JPerson_ID.ID FROM inserted)
WHERE Person.ID IN (SELECT JPerson_ID.PersonID FROM inserted)

When I create a new Person I get the following exception:

Violation of PRIMARY KEY constraint 'TBL_PERSON_A_PK'. 
Cannot insert duplicate key in object 'dbo.TBL_PERSON_A.

There is an update trigger associated to table Person that comes from a legacy component and I cannot edit or see it (it is encrypted). It seems the reason of the exception above.

Why do I get this exception even if I simply make an UPDATE and not an insert?

To solve this I disable such a trigger before executing the update and then enabling it again and it works like a charm: 

EXECUTE sp_executesql N'DISABLE TRIGGER dbo.TBL_PERSON_TRU1 ON PERSON'
...
EXECUTE sp_executesql N'ENABLE TRIGGER dbo.TBL_PERSON_TRU1 ON PERSON'

However how can I be sure that this will not bring to logic errors? Thanks.

Upvotes: 1

Views: 2268

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460228

I don't know if this causes your problem, but have you kept in mind that your (SELECT JPerson_ID.ID FROM inserted) could return more than one row?

So you insert trigger must be changed to somewhat like:

CREATE TRIGGER [dbo].[trgJPerson_ID] ON [dbo].[TBL_PERSON_A]
FOR INSERT
AS

INSERT INTO dbo.JPerson_ID(ID )
    SELECT ID FROM inserted

and accordingly the delete-trigger(if you have one)

CREATE TRIGGER [dbo].[trgJPerson_ID] ON [dbo].[TBL_PERSON_A]
FOR DELETE
AS

DELETE FROM dbo.JPerson_ID
    WHERE ID IN(SELECT ID FROM DELETED)

Upvotes: 1

codingbadger
codingbadger

Reputation: 44024

Unless I have misunderstood, you seem to have answered your own question; it appears that the trigger you can't see the definition of is doing something to prevent the update statement from completing.

Without knowing what that trigger does I can't see how we can help you.

As a side note, I don't understand why you are updating the Person table with the ID from the Person_ID table. It seems a bit pointless really as you can just join on to the Person_ID to retrieve the ID

Select p.ID, -- The Person GUID
       p.Name,
       pi.ID -- The Person_ID ID

From dbo.Person p
Join dbo.Person_ID pi on p.ID = pi.PersonID

Upvotes: 0

Related Questions