This really is in SQL Server 2005.
I've a previous address table:
dbo.Address ( AddressID INT IDENTITY(1, 1) PRIMARY KEY LastUpdateBy VARCHAR(30) <bunch of address columns> )
I in addition have a history table:
dbo.AddressHistory ( AddressID INT, AsOf DATETIME, UpdateBy VARCHAR(30) <all the address columns> CONSTRAINT PK_dbo_AddressHistory PRIMARY KEY CLUSTERED (AddressID, AsOf) )
I've got a trigger on dbo.Address to produce history records on Place increase that will essentially do that:
INSERT INTO dbo.AddressHistory(AddressID, AsOf, UpdateBy, <address columns>) SELECT AddressID, CURRENT_TIMESTAMP, @UpdateBy, <address columns> FROM INSERTED
But, once all the while, I'll obtain a PK breach on dbo.AddressHistory worrying in regards to a duplicate PK being placed. How's this possible if area of the PK for AddressHistory may be the current timestamp from the insertion?
Even performing this can place two rows in to the history table effectively:
INSERT INTO dbo.Address (LastUpdateBy, <address columns>) SELECT 'test', <address columns> FROM dbo.Address WHERE AddressID < 3
And also the only update sproc I've for that dbo.Address table will update a row for any given AddressID. Therefore it should simply be upgrading one row at any given time. My place sproc only card inserts one row at any given time too.
Any idea what conditions cause this to happen?
According to your description two concurrent accomplishments from the saved procedure with similar parameter would appear likely.
datetime has only a precision of
1/300 second so conflicts can happen if these accomplishments happen very close together.