维护自定义自动增量列 [英] maintaining a custom auto increment column
问题描述
我有一个带有 ID(身份)和 XID(整数)的表,这是我的自定义自动增量列.我正在使用而不是插入触发器来维护 XID,但我得到了重复项.
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.
表格
xtable (ID 身份,XID int)
xtable (ID identity, XID int)
触发器 - 而不是插入
insert into [xtable] (XID)
select [x].[NextavailableID]
from inserted [i]
cross apply
(
select coalesce(max([t].[XID]), 0) + 1 [NextavailableID]
from [xtable] [t]
) [x];
假设插入 = 1 行.
Assuming inserted = 1 row.
此触发器不会阻止 XID 列中的重复项.关于如何更改它的任何想法?
This trigger doesn't prevent duplicates in the XID column. Any ideas on how to change it?
推荐答案
我最终创建了另一个表来存储最后一个增量.在触发器中,在事务中,我从带有提示的新表中进行选择 (UPDLOCK
, ROWLOCK
).
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
).
表格
Info (LastId int)
<小时>
触发器 - 而不是插入
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]
这篇关于维护自定义自动增量列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!