1

I have a history table that is populated on trigger of another table and inserted data is all except 3 system columns, ID/CreatedBy/CreatedDate

Even though the ID PrimaryKey is an auto increment and the table is always populated without providing the value for ID(PK) and hence, ID will always be auto generated, we are getting primary key violation error.

Violation of PRIMARY KEY constraint 'PK_HistoryTablename'. Cannot insert duplicate key in object 'dbo.HistoryTablename'. The duplicate key value is (132137212).

What are the possible reasons for this to happen?

NOTE: For the purpose of this question, assume that the trigger query doesnt have a bug. I am looking for other reasons how it can happen at some points in time. The issue happens very rarely like once a year or so while the query runs on daily basis.

Because of popular demand, here's the sample trigger query.

ALTER TRIGGER [dbo].[tr_Tablename] 
   ON  [dbo].[Tablename] 
   AFTER INSERT ,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[HistoryTablename]
           ([Col1]
           ,[Col2]
           ,[Col3]
    SELECT [Col1]
           ,[Col2]
           ,[Col3]
    FROM Inserted
    WHERE NOT (SYSTEM_USER = 'aparticularsqluser'
    AND [Col2] = 'Y');

END

I really do appreciate everyone's input and i understand it may not have any straight-up answer so i am looking for what i may have missed to look at.

user3754372
  • 177
  • 16
  • `dbo.Tablename` make sure this is the name of your history table. Source table might have thrown this error as well – Pரதீப் Oct 03 '16 at 02:49
  • Yes, it is the history table, not the source table. I will modify the question's description to make it explicit. – user3754372 Oct 03 '16 at 02:52
  • Make sure History table has `Identity` property and values are not explicitly inserted into `ID` column through trigger – Pரதீப் Oct 03 '16 at 02:53
  • The ID is Auto increment Identity PK column and the trigger's insert query doesnt do insert in that column.. – user3754372 Oct 03 '16 at 02:56
  • It would help if you provided the DDL for the tables in question. – Cine Oct 03 '16 at 02:56
  • @user3754372 - As cine said, It will helpful if you add the `DDL` of both Source & History table and `Trigger` script – Pரதீப் Oct 03 '16 at 02:57
  • I cant provide actual data as it companies and the DDL and trigger script are truly irrelevant. – user3754372 Oct 03 '16 at 02:58
  • Have you checked the maximum value of the ID (PK) and the next value of the identity? Is it possible that the trigger is executed twice simultaneously and that's why you get this error? – Andrew Oct 03 '16 at 03:01
  • @Andrew The trigger doesnt do that check and i think it shouldnt have to unless you have any reason why it should. For the ID mentioned in the description, it was there in the target table but i dont know how that can happen since i assumed sql doesnt have race conditions like this for determining auto increment value. – user3754372 Oct 03 '16 at 03:04
  • @Andrew are you saying that if the trigger is triggered twice then the race condition can happened and is normal in SQL? – user3754372 Oct 03 '16 at 03:05
  • I don't have much experience on triggers, it was something I just thought. You maybe have a non-thread-safe query in your trigger. Can you compare the `CreatedDate` of the record with id 132137212 and the date you got that error? Theoretically they should be different days, right? – Andrew Oct 03 '16 at 03:07
  • @Andrew i can and they are on the same day around the same time but that doesnt help me. The inserts are multiple in a single day. Its batch which is processed sequentially and hence, there can be thousands of inserts in a day back to back. – user3754372 Oct 03 '16 at 03:09
  • Oh, I thought the trigger would run once a day. Can you post the script the trigger runs? – Andrew Oct 03 '16 at 03:10
  • @Andrew i added the sample query in description hoping it helps. – user3754372 Oct 03 '16 at 03:17
  • That's strange, your script looks quite atomic. Can you check there's no job or SP that may be doing something against that table's identity seed? – Andrew Oct 03 '16 at 04:00
  • 3
    Is it somehow related to this problem: http://dba.stackexchange.com/questions/68348/sequence-is-reusing ? More information is here: https://support.microsoft.com/en-us/kb/3011465 – cha Oct 03 '16 at 04:16
  • 2
    Possible reasons: * Somewhere someone is using IDENTITY_INSERT. * You are a sequence and not an auto increment. * You are reseeding the auto increment somewhere. * Multi server sync is enabled for the table – Cine Oct 03 '16 at 04:22
  • So the PK on the History table is an INT with IDENTITY(1,1) by chance? If so that rules out the possibility of a rollover, given the value you provided. Can you say where you are seeing this error please? Is it via a log someplace? – LogicalMan Oct 03 '16 at 13:50
  • @Cha you are onto something mate. That may be the case. – user3754372 Oct 03 '16 at 17:10
  • @Cine None of those things seem to be the case. – user3754372 Oct 03 '16 at 17:17
  • @LogicalMan It is [int] IDENTITY(1,1) PRIMARY KEY.I get the exception in the application doing the insert in the Tablename table as that insert triggers the insert in target HistoryTablename table. P.S. Lol nice username. – user3754372 Oct 03 '16 at 17:20
  • Related? https://connect.microsoft.com/SQLServer/feedback/details/778610/violation-of-primary-key-constraint-with-identity-column – Cine Oct 04 '16 at 04:26
  • @Cine yes its the same issue for sure. i have sql2012 too so makes sense. what Cha said is the official thread for that i am trying to confirm and apply the patch. – user3754372 Oct 04 '16 at 04:36
  • Interesting, are you using an IDENTITY or a defined SEQUENCE for the PK? see: http://stackoverflow.com/questions/10062328/sequence-vs-identity Ha, thanks re the name, someone gave me this moniker 18 years ago on a MSO forum. – LogicalMan Oct 11 '16 at 13:57

0 Answers0