SQL触发器校验影厅场次组合唯一性及排期范围功能失效排查

原有触发器的问题点 #

• 触发器类型错误：使用了AFTER Insert,Update，数据已经写入表后才执行校验，判断重复预约时会把刚插入的新数据算进去，逻辑完全错误，应该用INSTEAD OF触发器先校验再写入
• 批量操作失效：直接从inserted表给单变量赋值，当一次插入/更新多条数据时，只会取最后一条数据校验，其余数据都会跳过校验
• 重复预约判断逻辑错误：EXISTS (select count (Showtime_ID)...)不管计数是多少，EXISTS都会返回TRUE，另外没有排除当前操作的记录ID
• 上映日期校验逻辑写反：当前逻辑是「场次日期在影片上映区间内就报错」，和需求完全相反，应该是「不在区间内才报错」
• 语法错误：
  1. 给@Showtime_Date赋值时漏写变量前缀@，导致变量一直是NULL
  2. Movie表的上映日期字段拼写错误，建表时写的是Realease_Date（多了个e），触发器里写的是Release_Date，取值会失败
  3. 用双引号包裹带空格的表名兼容性差，建议统一用方括号[]

修正后的触发器代码 #

Create Trigger Trigger_Movie_Schedule
On [Movie Schedule]
INSTEAD OF Insert,Update
As
BEGIN
    SET NOCOUNT ON;
    -- 校验1：判断是否有同一影厅同场次的重复预约
    IF EXISTS (
        SELECT 1 FROM inserted i
        JOIN [Movie Schedule] ms ON i.Showtime_ID = ms.Showtime_ID 
            AND i.Cinema_Halls_ID = ms.Cinema_Halls_ID
            AND i.Movie_Schedule_ID != ms.Movie_Schedule_ID -- 排除自身记录（更新场景用）
    )
    BEGIN
        PRINT '该影厅对应场次已被预订'
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 校验2：判断场次是否在影片上下映日期范围内
    IF EXISTS (
        SELECT 1 FROM inserted i
        JOIN Showtime s ON i.Showtime_ID = s.Showtime_ID
        JOIN Movie m ON i.Movie_ID = m.Movie_ID
        WHERE s.Date < m.Realease_Date OR s.Date > ISNULL(m.Last_Date, '9999-12-31')
    )
    BEGIN
        PRINT '影片场次不在上映有效期范围内'
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 校验通过后执行实际的写入/更新操作
    IF EXISTS(SELECT 1 FROM deleted) -- 更新场景
    BEGIN
        UPDATE ms
        SET ms.Movie_ID = i.Movie_ID,
            ms.Showtime_ID = i.Showtime_ID,
            ms.Cinema_Halls_ID = i.Cinema_Halls_ID
        FROM [Movie Schedule] ms
        JOIN inserted i ON ms.Movie_Schedule_ID = i.Movie_Schedule_ID;
    END
    ELSE -- 插入场景
    BEGIN
        INSERT INTO [Movie Schedule] (Movie_Schedule_ID, Movie_ID, Showtime_ID, Cinema_Halls_ID)
        SELECT Movie_Schedule_ID, Movie_ID, Showtime_ID, Cinema_Halls_ID FROM inserted;
    END
END

额外优化建议 #

• 重复预约的校验其实可以直接加唯一约束实现，性能比触发器更好：ALTER TABLE [Movie Schedule] ADD CONSTRAINT UQ_Hall_Showtime UNIQUE (Cinema_Halls_ID, Showtime_ID)
• 建议修正Movie表的字段拼写错误，把Realease_Date改成Release_Date避免后续再出取值问题

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