如何使用触发器来允许递增的用户分配 ID?

Question

我正在将一个小型数据库从MS Access移动到SQL Server。每年，用户都会创建一个新的 Access 数据库并拥有干净的数据，但此更改会将多年来的数据放入一个锅中。用户依赖 Access 中的自动编号值作为记录的参考。如果删除了238条记录，这是非常不准确的。

所以我试图用他们可以控制的 id 列来容纳它们(有点)。他们不会在SQL表中看到真正的主键，但我想给他们一个可以编辑的ID，但仍然是唯一的。

我一直在使用这个触发器，但它花费的时间比我预期的要长得多。

一切似乎都工作正常，除了我不明白为什么我的插入表中的数据与触发器所在的表相同。(请参阅代码中的注释。

ALTER TRIGGER [dbo].[trg_tblAppData] 
ON  [dbo].[tblAppData] 
AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @NewUserEnteredId int = 0;
DECLARE @RowIdForUpdate int = 0;
DECLARE @CurrentUserEnteredId int = 0;
DECLARE @LoopCount int = 0;
--*** Loop through all records to be updated because the values will be incremented.
WHILE (1 = 1)
BEGIN
SET @LoopCount = @LoopCount + 1;
IF (@LoopCount > (SELECT Count(*) FROM INSERTED))
BREAK;
SELECT TOP 1 @RowIdForUpdate = ID, @CurrentUserEnteredId = UserEnteredId FROM INSERTED WHERE ID > @RowIdForUpdate ORDER BY ID DESC;
IF (@RowIdForUpdate IS NULL)
BREAK;
-- WHY IS THERE A MATCH HERE? HAS THE RECORD ALREADY BEEN INSERTED?
IF EXISTS (SELECT UserEnteredId FROM tblAppData WHERE UserEnteredId = @CurrentUserEnteredId)
BEGIN
SET @NewUserEnteredId = (SELECT Max(t1.UserEnteredId) + 1 FROM tblAppData t1);
END
ELSE
SET @NewUserEnteredId = @CurrentUserEnteredId;
UPDATE tblAppData
SET UserEnteredId = @NewUserEnteredId
FROM tblAppData a
WHERE a.ID = @RowIdForUpdate
END
END

以下是我想要完成的：

1. 添加新记录时，它应从"现有最大值"中递增值

2. 当用户覆盖某个值时，它应检查该值是否存在。如果找到，则还原现有值，否则允许更改。

此触发器允许一次添加多行。

这对于将来的使用是有效的，但实际上，他们每年只会添加 1,000 条记录。

Answer 1

我不会在使用触发器来完成此操作。

下面是一个脚本，可用于创建序列(op 未标记版本)、创建主键、将序列用作特殊 ID 以及对列施加约束。

create table dbo.test (
testid      int identity(1,1) not null primary key clustered 
,   myid        int null constraint UQ_ unique 
,   somevalue   nvarchar(255) null
);
create sequence dbo.myid
as int
start with 1
increment by 1;
alter table dbo.test
add default next value for dbo.myid for myid;
insert into dbo.test (somevalue)
select 'this' union all
select 'that' union all
select 'and' union all
select 'this';
insert into dbo.test (myid, somevalue)
select 33, 'oops';
select *
from dbo.test
insert into dbo.test (somevalue)
select 'oh the fun';
select *
from dbo.test
--| This should error
insert into dbo.test (myid, somevalue)
select 3, 'This is NO fun';

下面是结果集：

testid  myid    somevalue
1       1       this
2       2       that
3       3       and
4       4       this
5       33      oops
6       5       oh the fun

最后是一个测试，这将出错。