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