0

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

  • And where is DDL? What field(s) is a primary key? – Akina Nov 7 at 13:56
  • 6
    You could be inserting more than one row with your INSERT INTO ... SELECT FROM. That seems like the obvious possibility, anyway – Philᵀᴹ Nov 7 at 13:58
  • What @Philᵀᴹ said: how many rows are produced by this join WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER? Also, Why do the UPDATE when it is the same JOIN as the SELECT that inserted the row? Why not just set DATE_TO = GETDATE() in the insert? And, is the WHERE condition of DATA_DO IS NULL a valid column, or is there another trigger (an INSERT trigger) on this table as well? AND, in your SET statement using SELECT wartosc from AA_COUNTER where id_licznik = IdCounter, is "IdCounter" a column, or is that missing a @ to be = @IdCounter? Something doesn't look right there. – Solomon Rutzky Nov 7 at 14:27
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.