SQL插入多行时Stations_in_route表序号重编号触发器报错如何修复

问题根因 #

原有触发器默认单次仅插入1行数据，当单次向同一线路插入多条数据时，UPDATE语句中>=后的子查询会返回多个值，不符合SQL语法要求，因此抛出报错。

修复后的触发器 #

以下方案兼容单行/多行插入、多线路场景，完全符合需求：

create trigger stations_in_route_after_insert on Stations_in_route
after insert
as
begin
    SET NOCOUNT ON;
    -- 提取本次插入涉及的线路，不影响其他线路
    WITH AffectedRoutes AS (
        SELECT DISTINCT ID_route FROM inserted
    ),
    -- 统计每个线路需要偏移的次数
    OffsetCalc AS (
        SELECT
            s.ID_station_in_route,
            s.Number_in_route + (
                SELECT COUNT(*) 
                FROM inserted i 
                WHERE i.ID_route = s.ID_route AND s.Number_in_route >= i.Number_in_route
            ) AS New_Number_in_route
        FROM Stations_in_route s
        INNER JOIN AffectedRoutes ar ON s.ID_route = ar.ID_route
        WHERE s.ID_station_in_route NOT IN (SELECT ID_station_in_route FROM inserted)
    )
    -- 仅更新序号有变化的行
    UPDATE s
    SET s.Number_in_route = oc.New_Number_in_route
    FROM Stations_in_route s
    INNER JOIN OffsetCalc oc ON s.ID_station_in_route = oc.ID_station_in_route
    WHERE s.Number_in_route <> oc.New_Number_in_route;
end

逻辑说明 #

1. 仅处理本次插入操作涉及的线路，不会修改其他线路的站点序号
2. 对每个原有站点，统计同线路下插入的站点中序号小于等于当前原有站点序号的数量，该数量就是当前站点需要向后偏移的位数，自动适配单次插入多条的场景
3. 仅更新序号有变化的行，减少不必要的IO开销，性能更优
4. 完全匹配你给出的插入示例预期结果：插入ID_route=4的两个站点后，原有站点序号3偏移1位变为4，原有站点序号4偏移2位变为6，最终序号连续。

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