CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2014
Aducci
Aducci

Reputation: 26654

maintaining a custom auto increment column

I have a table with ID (identity) and XID (int) which is my custom auto increment column. I am using an instead of insert trigger to maintain XID, but I am getting duplicates.

table

xtable (ID identity, XID int)

trigger - instead of insert

insert into [xtable] (XID)
select [x].[NextavailableID]
from inserted [i]
cross apply
(
  select coalesce(max([t].[XID]), 0) + 1 [NextavailableID]
  from [xtable] [t]
) [x];

Assuming inserted = 1 row.

This trigger doesn't prevent duplicates in the XID column. Any ideas on how to change it?

Upvotes: 0

Views: 1034

Answers (2)

Aducci
Aducci

Reputation: 26654

I ended up creating another table to store the last increment. In the trigger, inside a transaction, I select from the new table with hints (UPDLOCK, ROWLOCK).

table

Info (LastId int)

trigger - instead of insert

declare @nextId int;

begin tran t1

  set @nextId = (select top 1 LastId from Info with (UPDLOCK, ROWLOCK)) + 1;

  update Info set LastId = nextId;

commit tran t1

insert into [xtable] (XID)
select @nextId
from inserted [i]

Upvotes: 1

GarethD
GarethD

Reputation: 69769

The problem is that where you have multiple rows being inserted you are using the same next available ID for all rows, you'd need to add ROW_NUMBER() in to ensure the xid was unique in the insert:

insert into [xtable] (XID)
select [x].[NextavailableID] + ROW_NUMBER() OVER (ORDER BY i.ID)
from inserted [i]
cross apply
(
  select coalesce(max([t].[XID]), 0) [NextavailableID]
  from [xtable] [t] WITH (TABLOCK, HOLDLOCK)
) [x];

With regard to preventing duplicates you can use table hints to lock xtable when getting the maximum xid.

The drawback of using these locks is that you will get deadlocking. You should have a unique constraint/index on this column as this will prevent duplicates, however it will also result in exceptions when a race condition is met. Ultimately, whatever method you choose you will need to make some kind of sacrifice.

Upvotes: 2

Related Questions