Oracle SQL中利用触发器与自定义序列实现父子项编号关联

嘿，这个需求很常见，我之前帮不少人解决过类似的子项编号关联父项的问题。核心是得让每个父项的子项后缀独立递增，不能跟其他父项的子项混在一起。下面给你两种实用的实现方案，都是基于触发器的，你可以根据自己的数据库类型和性能需求选：

要实现子项编号（比如P2505-1）和父项编号关联且独立递增，关键是针对每个父项的子项数量做实时、独立的计数——不能用全局自增列，否则不同父项的子项后缀会乱序。

这种方式适合子项插入频率不高的场景，每次插入子项时，直接统计当前父项已有的子项数量，加1作为后缀。

以SQL Server为例，触发器代码如下：

CREATE TRIGGER trg_SubItem_GenerateRefNo
ON SubItems
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 给刚插入的子项生成关联编号
    UPDATE si
    SET si.Sub_Ref_No = p.Ref_No + '-' + CAST(
        -- 统计当前父项的所有子项数量（包含刚插入的这条）
        (SELECT COUNT(*) FROM SubItems WHERE Parent_Id = si.Parent_Id)
        AS VARCHAR(10)
    )
    FROM SubItems si
    INNER JOIN INSERTED i ON si.SubItem_Id = i.SubItem_Id
    INNER JOIN ParentItems p ON si.Parent_Id = p.Parent_Id;
END

逻辑说明：触发器在子项插入后触发，关联父项获取对应的Ref_No，然后统计该父项下的所有子项数量，把数量转换成字符串拼接到父项编号后面，得到子项的最终编号。

如果子项插入频率很高，方案1里的COUNT(*)会因为频繁扫描表导致性能下降。这时候可以在父项表加一个专门的计数字段，维护对应子项的数量，用原子更新保证计数准确。

步骤1：给父项表添加计数字段 #

ALTER TABLE ParentItems ADD SubItem_Count INT DEFAULT 0;

步骤2：创建子项触发器 #

CREATE TRIGGER trg_SubItem_GenerateRefNo_WithCount
ON SubItems
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 先原子更新父项的子项计数（避免并发插入时的计数错误）
    UPDATE p
    SET p.SubItem_Count = p.SubItem_Count + 1
    FROM ParentItems p
    INNER JOIN INSERTED i ON p.Parent_Id = i.Parent_Id;

    -- 用更新后的计数生成子项编号
    UPDATE si
    SET si.Sub_Ref_No = p.Ref_No + '-' + CAST(p.SubItem_Count AS VARCHAR(10))
    FROM SubItems si
    INNER JOIN INSERTED i ON si.SubItem_Id = i.SubItem_Id
    INNER JOIN ParentItems p ON si.Parent_Id = p.Parent_Id;
END

逻辑说明：每次插入子项时，先给对应父项的计数字段加1（这是原子操作，并发场景下也不会出现重复计数），再用这个最新的计数作为后缀拼接父项编号，性能比方案1好很多。

• 并发场景处理：如果有多个子项同时插入同一个父项，方案1的COUNT(*)可能因为事务隔离级别出现计数重复。这时候可以改用ROW_NUMBER()按父项分区排序的方式：

  UPDATE si
  SET si.Sub_Ref_No = p.Ref_No + '-' + CAST(
      ROW_NUMBER() OVER(PARTITION BY si.Parent_Id ORDER BY si.SubItem_Id)
      AS VARCHAR(10)
  )
  FROM SubItems si
  INNER JOIN INSERTED i ON si.SubItem_Id = i.SubItem_Id
  INNER JOIN ParentItems p ON si.Parent_Id = p.Parent_Id;
  

  这种方式会给每个父项的子项按插入顺序生成唯一的序号，并发场景下更可靠。
• 编号唯一性：如果需要保证Sub_Ref_No全局唯一，可以给这个字段加唯一约束，确保不会出现重复编号。
• 数据库兼容性：上面的示例是SQL Server语法，如果是MySQL，触发器语法会有差异（比如用NEW关键字代替INSERTED），但核心逻辑完全通用。

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