SQL Server 2019 中可用触发器生成自定义单号（如 CP00001），但需用 TABLOCKX+HOLDLOCK 避免并发冲突，适合低并发、强业务规则场景；高吞吐系统应优先考虑 SEQUENCE + DEFAULT 或应用层生成。
直接说结论：SQL Server 2019 中用触发器生成自定义单号（如 CP00001、ORD2026001）可行，但必须避开 IDENTITY 列冲突、并发竞争和事务回滚导致的编号重复或跳号问题；它适合低并发、业务逻辑强耦合编号规则的场景，不适合高吞吐订单系统。
为什么不用 IDENTITY 而选触发器
因为 IDENTITY 只能生成纯数字递增整数（如 1, 2, 3），无法满足带前缀、年份、补零、多级编码等业务单号需求（如 INV-2026-00047）。触发器在 AFTER INSERT 或 INSTEAD OF INSERT 阶段可控拼接字符串，是唯一能在 SQL Server 原生支持下实现“格式化自增”的方式。
常见错误现象：
– 插入后发现单号全是 CP00001（未加事务锁，多用户同时读到 MAX() 为 NULL）；
– 并发插入时生成相同单号（如两个会话都算出 CP00005）；
– 事务回滚后，单号已“被占用”，出现空洞且不可重用。
- 务必使用
SELECT ... FROM table WITH (TABLOCKX, HOLDLOCK)强制串行化读取最大值 - 避免在触发器里调用
MAX(code)直接查表——没锁就是灾难 - 不要依赖
@@IDENTITY或SCOPE_IDENTITY()，它们对非IDENTITY列无效
AFTER INSERT 触发器 + 补零单号的实操写法
以生成 CP00001 格式为例，假设表 orders 有字段 id（主键，INT IDENTITY）和 order_no（NVARCHAR(20)，允许 NULL）：
CREATE TRIGGER trg_gen_order_no
ON orders
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @maxNo INT, @newNo NVARCHAR(20);
<pre class="brush:php;toolbar:false;">-- 加锁读取当前最大编号数字部分（关键！）
SELECT @maxNo = ISNULL(MAX(CAST(SUBSTRING(order_no, 3, LEN(order_no)-2) AS INT)), 0)
FROM orders WITH (TABLOCKX, HOLDLOCK)
WHERE order_no LIKE 'CP%';
SET @maxNo = @maxNo + 1;
SET @newNo = 'CP' + RIGHT('00000' + CAST(@maxNo AS VARCHAR(5)), 5);
-- 更新刚插入的行（仅限单行插入；批量需用 JOIN inserted）
UPDATE o
SET order_no = @newNo
FROM orders o
INNER JOIN inserted i ON o.id = i.id;END;
注意点：
– TABLOCKX（排他表锁）+ HOLDLOCK（等价于 SERIALIZABLE）确保读写不交叉；
– 此写法只支持单行 INSERT；若需支持批量插入，必须用 UPDATE ... FROM orders o INNER JOIN inserted i 配合窗口函数或游标；
– LEN(order_no)-2 是硬编码，实际应提取前缀长度配置，否则换前缀就崩。
更安全的替代方案：序列（SEQUENCE）+ 默认约束
SQL Server 2012+ 支持 SEQUENCE，比触发器更轻量、无锁竞争、天然支持缓存。虽然不能直接拼前缀，但可结合 DEFAULT 约束 + 计算列或应用层组装：
-- 1. 创建序列
CREATE SEQUENCE seq_order_id
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CACHE;
<p>-- 2. 修改表，让 order_no 由默认值生成（需先清空数据或用计算列）
ALTER TABLE orders
ADD CONSTRAINT DF_order_no DEFAULT
('CP' + RIGHT('00000' + CAST(NEXT VALUE FOR seq_order_id AS VARCHAR(5)), 5))
FOR order_no;
优点：
– NEXT VALUE FOR 是原子操作，无需手动加锁；
– 支持 CACHE 提升性能（但崩溃可能丢号）；
– 可跨表复用同一序列（如 CP 和 INV 共享一个计数器）。
限制：
– DEFAULT 约束不能引用其他列，所以前缀必须写死；
– 若需动态前缀（如按年切分 CP2026001），仍得回到触发器或应用层生成。
容易被忽略的并发与维护坑
真正上线时最常栽跟头的地方不是语法，而是这些细节：
- 触发器中没写
SET NOCOUNT ON→ 应用层误判影响行数，ORM 报错 - 用
BEFORE INSERT（SQL Server 不支持）→ 实际只能用AFTER或INSTEAD OF，后者需重写整个插入逻辑 - 没处理
inserted多行情况 → 批量导入时只更新第一行，其余order_no为 NULL - 重建表或
TRUNCATE后，序列不会自动重置 → 必须手动ALTER SEQUENCE ... RESTART WITH 1 - 数据库镜像或 AG 环境下，触发器执行时机可能受同步延迟影响 → 建议单号生成尽量下沉到应用层或用分布式 ID 服务
如果单号要进财务或合同系统，别省事——触发器生成的“自增”本质是业务规则模拟，不是数据库原生保障，任何环节出错都可能导致编号冲突，务必在应用层加唯一索引 + 冲突重试逻辑。
