使用触发器模拟 SQL Server 2005 中的第二个标识列 [英] Using a trigger to simulate a second identity column in SQL Server 2005

问题描述

我有多种原因需要在 SQL Server 2005 数据库中实现，除了标识列 PK，第二个并发安全的自动递增列.能够拥有多个标识列是理想的，但我正在考虑使用触发器来模拟这一点，尽可能接近金属.

I have various reasons for needing to implement, in addition to the identity column PK, a second, concurrency safe, auto-incrementing column in a SQL Server 2005 database. Being able to have more than one identity column would be ideal, but I'm looking at using a trigger to simulate this as close as possible to the metal.

我相信我必须在触发器中使用可序列化的隔离级别事务.我是否会像在普通 SQL 查询中使用这样的事务那样进行处理?

I believe I have to use a serializable isolation level transaction in the trigger. Do I go about this like Ii would use such a transaction in a normal SQL query?

第二个递增列的业务含义与第一个、PK、递增列的幕后含义保持分离，这是一个不可协商的要求.

尽可能简单地说，如果我创建 JobCards '0001'、'0002' 和 '0003'，然后删除 JobCards '0002' 和 '0003'，我创建的下一个 Jobcard 的 ID 必须为 '0002'，而不是 '0004'.

To put things as simply as I can, if I create JobCards '0001', '0002', and '0003', then delete JobCards '0002' and '0003', the next Jobcard I create must have ID '0002', not '0004'.

推荐答案

这可能是一个糟糕的主意，但它至少适用于有限的使用场景

只需使用常规身份并在删除时重新播种.

Just use a regular identity and reseed on deletes.

create table reseedtest (
   a int identity(1,1) not null,
   name varchar(100)
)

insert reseedtest values('erik'),('john'),('selina')
select * from reseedtest

go
CREATE TRIGGER TR_reseedtest_D ON reseedtest FOR DELETE
AS
BEGIN TRAN
DECLARE @a int
SET @a = (SELECT TOP 1 a FROM reseedtest WITH (TABLOCKX, HOLDLOCK))
--anyone know another way to lock a table besides doing something to it?
DBCC CHECKIDENT(reseedtest, reseed, 0)
DBCC CHECKIDENT(reseedtest, reseed)
COMMIT TRAN
GO

delete reseedtest where a >= 2
insert reseedtest values('katarina'),('david')
select * from reseedtest

drop table reseedtest

如果您从堆栈的中间"删除，这将不起作用，但它适用于从递增端删除.

This won't work if you are deleting from the "middle of the stack" as it were, but it works fine for deletes from the incrementing end.

重新播种一次到 0 然后再次播种只是避免必须计算正确的重新播种值的一个技巧.

Reseeding once to 0 then again is just a trick to avoid having to calculate the correct reseed value.

这篇关于使用触发器模拟 SQL Server 2005 中的第二个标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！