SQL Server 编写Reservations插入后仅更新对应FLIGHT_ID的Number_Seats行的触发器

• 原代码核心错误：直接关联全量Reservations业务表，而非本次插入操作触发的临时记录集，导致匹配所有历史预订对应的航班行
• SQL Server INSERT类触发器内置inserted临时表：仅存储本次触发触发器的所有新插入记录，是精准定位目标行的核心依据
• 原代码中JOIN Flights的逻辑为冗余操作，Number_Seats与inserted表均含FLIGHT_ID字段，可直接关联

适配作业单条插入场景（最小修改） #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[ajSad]
ON [dbo].[Reservations]
AFTER INSERT 
AS
BEGIN
    -- 跳过无有效插入的无效执行
    IF NOT EXISTS (SELECT 1 FROM inserted)
        RETURN

    UPDATE Number_Seats
    SET Total_Number_Seats = Total_Number_Seats - 1
    FROM Number_Seats 
    -- 仅关联本次插入的临时记录表，而非全量Reservations表
    INNER JOIN inserted ON Number_Seats.FLIGHT_ID = inserted.FLIGHT_ID
END

兼容批量插入场景（推荐，符合SQL Server最佳实践） #

若作业要求支持一次插入多条预订（如批量导入），需按本次插入的同航班预订数扣减，避免仅减1的错误：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[ajSad]
ON [dbo].[Reservations]
AFTER INSERT 
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM inserted)
        RETURN

    UPDATE ns
    SET Total_Number_Seats = Total_Number_Seats - GroupedInsert.InsertCount
    FROM Number_Seats ns
    -- 按航班分组统计本次插入的预订数量
    INNER JOIN (
        SELECT FLIGHT_ID, COUNT(*) AS InsertCount
        FROM inserted
        GROUP BY FLIGHT_ID
    ) GroupedInsert ON ns.FLIGHT_ID = GroupedInsert.FLIGHT_ID
END

使用给定的测试插入语句执行：

INSERT INTO reservations (ID_Rezervacije, ID_Klijenta, FLIGHT_ID)
VALUES (5, 5, 5);

仅会更新Number_Seats表中FLIGHT_ID = 5的对应行，其余航班行不受影响。

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