如何在SQL Server中为子记录设置按父级重置的自增列？

1. 创建SalesOrderDetail表结构 #

首先定义表结构，指定(TxnNumber, TxnDetailID)为复合主键，并建立与SalesOrderHeader的外键关联：

CREATE TABLE SalesOrderDetail (
    TxnDetailID INT NOT NULL,
    TxnNumber VARCHAR(20) NOT NULL,
    -- 按需添加其他订单明细字段，例如：
    -- ProductID INT NOT NULL,
    -- Quantity INT NOT NULL,
    CONSTRAINT PK_SalesOrderDetail PRIMARY KEY (TxnNumber, TxnDetailID),
    CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader 
        FOREIGN KEY (TxnNumber) REFERENCES SalesOrderHeader(TxnNumber)
);

2. 创建INSTEAD OF INSERT触发器 #

通过触发器自动计算每个TxnNumber对应的自增TxnDetailID，同时支持批量插入和并发安全：

CREATE TRIGGER trg_SalesOrderDetail_Insert
ON SalesOrderDetail
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 为插入的记录按TxnNumber分组生成行号
    WITH InsertedWithRowNum AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY TxnNumber ORDER BY (SELECT NULL)) AS RowNum
        FROM inserted
    ),
    -- 获取每个TxnNumber当前的最大TxnDetailID（加UPDLOCK防止并发冲突）
    MaxDetailIDs AS (
        SELECT 
            TxnNumber,
            COALESCE(MAX(TxnDetailID), 0) AS MaxID
        FROM SalesOrderDetail WITH (UPDLOCK)
        GROUP BY TxnNumber
    )
    -- 插入数据并生成自增的TxnDetailID
    INSERT INTO SalesOrderDetail (TxnDetailID, TxnNumber /*, 其他字段 */)
    SELECT 
        COALESCE(m.MaxID, 0) + iwr.RowNum AS TxnDetailID,
        iwr.TxnNumber
        /*, iwr.ProductID, iwr.Quantity  -- 替换为实际的其他字段 */
    FROM InsertedWithRowNum iwr
    LEFT JOIN MaxDetailIDs m ON iwr.TxnNumber = m.TxnNumber;
END;

3. 关键说明 #

• 批量插入支持：使用ROW_NUMBER()函数处理同一TxnNumber的多条插入记录，确保生成连续的自增ID。
• 并发安全：在查询最大ID时添加WITH (UPDLOCK)，锁定对应TxnNumber的记录，避免多个会话同时插入时出现主键冲突。
• 外键约束：确保插入的TxnNumber在SalesOrderHeader表中存在，避免违反外键规则。

测试示例 #

插入测试数据时，无需手动指定TxnDetailID，触发器会自动生成：

-- 插入TxnNumber为00001的两条明细
INSERT INTO SalesOrderDetail (TxnNumber) VALUES ('00001'), ('00001');

-- 插入TxnNumber为00002的一条明细
INSERT INTO SalesOrderDetail (TxnNumber) VALUES ('00002');

-- 查询结果
SELECT TxnDetailID, TxnNumber FROM SalesOrderDetail;

查询结果会符合预期：

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