如何让SQL触发器的Externalid字段从指定值开始自增

如果该字段本身就是SQL Server自带的自增列，直接重置自增起始值即可：

• 先查询当前自增列的最大值，避免重置后出现重复值冲突

SELECT IDENT_CURRENT('persons') AS 当前Externalid自增最大值

• 执行重置命令，将自增基准设为45，后续插入的第一条数据自增取值就是46，触发器拼接后自动得到MN46

DBCC CHECKIDENT ('persons', RESEED, 45)

注意：如果表中已有数据的Externalid值大于45，不要执行上述重置命令，会触发唯一值冲突。

如果不想修改原表的自增属性，或者需要更灵活的序号规则，用SQL Server 2012及以上支持的SEQUENCE实现：

• 第一步：创建指定起始值的序列

CREATE SEQUENCE Externalid_Sequence
START WITH 46 -- 第一条取46，拼接后为MN46
INCREMENT BY 1 -- 每次递增1
NO CYCLE -- 达到最大值后不循环
CACHE 10 -- 缓存10个序号提升插入性能

• 第二步：修改原有触发器逻辑，替换原有取值逻辑为序列取值

ALTER TRIGGER test
   ON  persons
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE t1
        SET Externalid = 'MN' + CAST(NEXT VALUE FOR Externalid_Sequence AS VARCHAR(20))
    FROM inserted AS t
        INNER JOIN Persons AS t1 ON t.PersonUid = t1.PersonUid
END

如果使用的是SQL Server 2012以下版本，自建序号维护表实现自增：

• 第一步：创建序号维护表并初始化

CREATE TABLE CustomSequence (
    SeqName VARCHAR(50) PRIMARY KEY,
    CurrentVal INT NOT NULL
)
-- 初始值设为45，第一条取的时候+1得到46
INSERT INTO CustomSequence VALUES ('Externalid_Seq', 45)

• 第二步：修改触发器，原子更新序号表避免并发冲突（支持批量插入）

ALTER TRIGGER test
   ON  persons
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @InsertCount INT = @@ROWCOUNT
    DECLARE @StartSeq INT

    -- 原子更新获取本次批量插入的起始序号
    UPDATE CustomSequence
    SET @StartSeq = CurrentVal + 1,
        CurrentVal = CurrentVal + @InsertCount
    WHERE SeqName = 'Externalid_Seq'

    -- 为批量插入的每条数据分配连续序号
    ;WITH InsertedRows AS (
        SELECT PersonUid, ROW_NUMBER() OVER(ORDER BY PersonUid) AS RowNum
        FROM inserted
    )
    UPDATE t1
        SET Externalid = 'MN' + CAST(@StartSeq + RowNum - 1 AS VARCHAR(20))
    FROM InsertedRows ir
    INNER JOIN Persons t1 ON ir.PersonUid = t1.PersonUid
END

内容的提问来源于stack exchange，提问作者Div26