MS Access迁移SQL Server：如何用触发器实现用户可控递增ID？

嘿，这个需求我之前帮客户处理过类似的，刚好能给你一套可行的方案。核心思路是：用一个内部真实主键（用户完全看不到）保证数据唯一性，再用一个用户可见的可控ID列，通过触发器自动生成递增值，同时允许用户手动指定ID。具体步骤如下：

1. 基础表结构设计 #

首先需要两张表：一张是你的业务数据表，一张是用来追踪当前最大可控ID的序列表（解决并发冲突问题）。

序列追踪表（管理ID序列） #

这个表用来存储每个业务表的当前最大可控ID，确保多用户同时插入时不会生成重复值：

CREATE TABLE SequenceTracker (
    TableName NVARCHAR(128) PRIMARY KEY, -- 关联对应的业务表名
    CurrentMaxID INT NOT NULL DEFAULT 0 -- 当前已使用的最大用户可控ID
);

-- 初始化你的业务表的序列（替换成你的实际表名）
INSERT INTO SequenceTracker (TableName, CurrentMaxID)
VALUES ('YourBusinessTable', 0);

业务数据表 #

包含内部主键（用户不可见）、用户可控ID（加唯一约束防重复）和业务列：

CREATE TABLE YourBusinessTable (
    -- 内部真实主键：用IDENTITY自动生成，用户完全不用接触
    InternalID INT IDENTITY(1,1) PRIMARY KEY,
    -- 用户可控ID：唯一约束确保不会重复，允许用户手动指定
    UserControlledID INT NOT NULL UNIQUE,
    -- 以下是你的业务列，按需添加
    CustomerName NVARCHAR(100),
    OrderDate DATE,
    Amount DECIMAL(18,2)
);

2. 创建INSERT触发器 #

触发器的作用是：当用户插入数据时，如果没有指定UserControlledID，自动生成下一个递增ID；如果用户指定了，就用用户提供的值，同时更新序列表的最大ID。

支持单行/多行插入的触发器代码 #

CREATE TRIGGER trg_YourBusinessTable_AssignUserID
ON YourBusinessTable
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 开启事务并锁定序列表，防止并发插入导致重复ID
    BEGIN TRANSACTION;

    DECLARE @CurrentMaxID INT;
    -- 获取当前最大可控ID（加锁保证并发安全）
    SELECT @CurrentMaxID = CurrentMaxID
    FROM SequenceTracker WITH (UPDLOCK, HOLDLOCK)
    WHERE TableName = 'YourBusinessTable';

    -- 处理插入：自动生成未指定的ID，保留用户指定的ID
    WITH AutoIDGenerator AS (
        SELECT 
            *,
            -- 为未指定ID的行生成连续递增的ID
            @CurrentMaxID + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS AutoID
        FROM inserted
        WHERE UserControlledID IS NULL
    )
    INSERT INTO YourBusinessTable (UserControlledID, CustomerName, OrderDate, Amount)
    SELECT 
        -- 优先用用户指定的ID，没有的话用自动生成的
        COALESCE(i.UserControlledID, aig.AutoID),
        i.CustomerName,
        i.OrderDate,
        i.Amount
    FROM inserted i
    LEFT JOIN AutoIDGenerator aig 
        ON i.CustomerName = aig.CustomerName 
        AND i.OrderDate = aig.OrderDate 
        AND i.Amount = aig.Amount; -- 用业务列关联，确保匹配正确

    -- 更新序列表的最大ID：取当前业务表的实际最大ID，兼容用户手动插入的大ID
    UPDATE SequenceTracker
    SET CurrentMaxID = (SELECT MAX(UserControlledID) FROM YourBusinessTable)
    WHERE TableName = 'YourBusinessTable';

    COMMIT TRANSACTION;
END;

3. 给用户提供访问视图（隐藏内部主键） #

用户不需要看到内部主键InternalID，所以创建一个视图只暴露他们需要的列：

CREATE VIEW vw_YourBusinessData
AS
SELECT UserControlledID, CustomerName, OrderDate, Amount
FROM YourBusinessTable;

然后给用户授权访问这个视图，而不是直接访问业务表，确保内部主键完全隐藏。

4. 关键使用场景说明 #

• 自动生成ID：用户插入数据时不指定UserControlledID，触发器会自动生成下一个递增ID

  INSERT INTO vw_YourBusinessData (CustomerName, OrderDate, Amount)
  VALUES ('ABC Corp', '2024-05-20', 1500.00);
  

• 手动指定ID：用户可以直接插入自定义的ID（只要不重复）

  INSERT INTO vw_YourBusinessData (UserControlledID, CustomerName, OrderDate, Amount)
  VALUES (1001, 'XYZ Ltd', '2024-05-21', 2000.00);
  

• 调整序列起始值：如果用户想让下一个自动ID从某个值开始，直接更新序列表即可

  UPDATE SequenceTracker SET CurrentMaxID = 1500 WHERE TableName = 'YourBusinessTable';
  -- 下一个自动生成的ID就是1501
  

注意事项 #

• 一定要给UserControlledID加UNIQUE约束，防止用户手动插入重复值
• 触发器里的UPDLOCK和HOLDLOCK是并发安全的关键，避免多用户同时插入时生成重复ID
• 如果需要支持修改UserControlledID，可以添加UPDATE触发器来检查修改后的值是否唯一，或者让用户自行负责（因为是“可控”ID）

内容的提问来源于stack exchange，提问作者Andy In NC