Sql-server – Violation of PRIMARY KEY constraint when there is no duplicate

sql servert-sql

I inherited an old sql server data base, and I need to create trigger that copy particular row with new dates to the same table. I have to manually set Id of objects and I am getting Violation of PRIMARY KEY although there is no record with that Id in the table( I even tried to set Id to ridiculously high value, but error is the same)

CREATE TRIGGER [dbo].[tr_HR_WORKER_UPDATED]
   ON [server].[dbo].[HR_WORKER]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (ID_ORGANIZATION) 
    BEGIN

        DECLARE @IdCounter NUMERIC(15, 4)
        DECLARE @NextId NUMERIC(15, 4) --Yes, Id is numeric
        SET @IdCounter = 148
        SET @NextId = (SELECT wartosc from AA_COUNTER where id_licznik = IdCounter) + 1.0000 --I have to get ID from other table, increment, and update that table
        select @NextId

        UPDATE AA_COUNTER--updating counter
        SET wartosc = @NextId
        WHERE id_licznik = @IdCounter

        INSERT INTO TR_ASSIGNMENT_ORG(
                                   [ID_ASSIGNMENT_ORG]
                                  ,[FIELD1]
                                  ,[FIELD2]
                                  ,[FIELD3]
                                  ,[DATE_FROM]
                                  ,[DATE_TO]
                                  ,[ID_RESPONSIBLE_WORKER]
                                  ,[ID_WORKER]
                                  ,[ID_STRUCT_ORG]
                                  ,[STACJA]
                                  ,[CHANGE_DATE]
                                  )
        SELECT                    
                                   @NextId
                                  ,P.[FIELD1]
                                  ,P.[FIELD2]
                                  ,P.[FIELD3]
                                  ,GETDATE()
                                  ,NULL
                                  ,P.[ID_RESPONSIBLE_WORKER]
                                  ,P.[ID_WORKER]
                                  ,I.[ID_STRUCT_ORG]
                                  ,P.[STACJA]
                                  ,GETDATE() -- I can execute code to this line
                                  FROM dbo.TR_ASSIGNMENT_ORG as P, inserted as I
                                  WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER

        UPDATE TR_ASSIGNMENT_ORG
        SET DATE_TO = GETDATE()
        FROM inserted I
        WHERE TR_ASSIGNMENT_ORG.ID_RESPONSIBLE_WORKER = I.ID_WORKER 
        AND TR_ASSIGNMENT_ORG.DATA_DO IS NULL

    END 
END

Can someone explain to me why sql server throws "Violation of PRIMARY KEY X" when there is no key with this value in table?

ID_ASSIGNMENT_ORG is primary key, and i want to assign @NextId to it

Best Answer

Do you have recursive triggers set? If so you might be re-firing the trigger from inside. In any case, as previous commenters have noted: you are generating a single ID value, but a trigger may get fired (or your internal join may produce) more than 1 row to be put in the table. There is nothing to stop this clause:

FROM dbo.TR_ASSIGNMENT_ORG as P, inserted as I
    WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER

from returning many records.

Ideally you would to find a way to remove the ID generation from the inside of the trigger (eg, convert the ID to an identity & set the seed above the current high watermark of the ID column) - may require app change if records are not inserted via stored proc which are in your gift to change.

You might consider aborting if you are given multiple records (insert count > 1). If you (experimentally) add that and still have a problem then for sure it's your trigger code thats generating the additional inserts.

Related Question