如何创建SQL触发器防止讲师同一时间任教不同班级

需要在Class表实现业务校验规则：禁止同一名讲师在同一时间任教不同Class_ID对应的班级，当前编写的触发器逻辑错误无法生效。

现有表结构 #

CREATE TABLE Class
(
    Class_ID BIGINT,
    c_InstrumentID BIGINT NOT NULL,
    c_StudentID BIGINT,
    c_InstructorID BIGINT NOT NULL,
    c_InstituteId BIGINT NOT NULL,
    c_TermSeason NVARCHAR(10),
    c_TermYear INT,
    c_TimeOfClass TIME NOT NULL,
    c_DayOfClass NVARCHAR(30),
    c_Eligibility INT,
    c_RemainingSession INT,

    CONSTRAINT cons_Season 
        CHECK(c_TermSeason IN ('Spring', 'Summer', 'Fall', 'Winter')),
    CONSTRAINT cons_TimeClass 
        CHECK(c_TimeOfClass BETWEEN '08:30:00' AND '20:30:00'),
    CONSTRAINT cons_RemainSession 
        CHECK (c_RemainingSession BETWEEN 0 AND 12),

    FOREIGN KEY(c_InstrumentID) 
        REFERENCES Instrument(Instrument_ID) ON DELETE NO ACTION,
    FOREIGN KEY(c_StudentID) 
        REFERENCES Student(Student_ID) ON DELETE NO ACTION,
    FOREIGN KEY(c_InstructorID) 
        REFERENCES Instructor(Instructor_ID) ON DELETE NO ACTION,
    FOREIGN KEY(c_InstituteId) 
        REFERENCES Institute(Institute_ID) ON DELETE NO ACTION,

    PRIMARY KEY (Class_ID)
)

原有触发器的逻辑错误 #

原有触发器完全无法实现校验，核心问题有4个：

• 条件判断逻辑完全写反：所有时间维度字段都用了!=匹配，还套了NOT EXISTS判断，和“相同讲师、相同时间维度才判定冲突”的需求完全相悖
• 待校验数据取错：触发器里的inserted虚拟表才是本次插入/更新的新数据，原有逻辑反而把全表排除inserted的旧数据当成新数据做比对
• 没有覆盖更新场景：只绑定了AFTER INSERT事件，后续修改讲师、上课时间字段时产生的冲突不会被校验
• 没有处理批量插入的场景，也没有排除同一条记录和自身的比对，很容易出现误判或者漏判

方案1：触发器实现（兼容所有写入场景） #

触发器需要同时覆盖插入、更新事件，校验逻辑为：只要存在和新记录讲师ID相同、学年相同、学期相同、上课星期相同、上课时间相同、但班级ID不同的记录，就判定为冲突，直接回滚并抛出明确错误。

CREATE OR ALTER TRIGGER trg_CheckInstructorClassConflict
ON Class
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted new_data
        INNER JOIN Class exist_data
            ON new_data.c_InstructorID = exist_data.c_InstructorID
            AND new_data.c_TermYear = exist_data.c_TermYear
            AND new_data.c_TermSeason = exist_data.c_TermSeason
            AND new_data.c_DayOfClass = exist_data.c_DayOfClass
            AND new_data.c_TimeOfClass = exist_data.c_TimeOfClass
            AND new_data.Class_ID <> exist_data.Class_ID
    )
    BEGIN
        RAISERROR('业务校验失败：同一名讲师不允许在同一时间段任教多个班级', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

这个写法的优势：

• 自动兼容单条插入、批量插入、更新操作的校验，既会校验新数据和原有旧数据的冲突，也会校验本次批量写入的多条新数据之间的冲突
• 排除了同一条记录和自身比对的情况，不会出现无意义的误判
• 抛出明确的错误提示，方便上层业务定位问题

方案2：唯一索引实现（性能更优，可靠性更高） #

如果业务上允许，优先用唯一索引实现这类约束，比触发器性能更好，不会出现触发器漏判、执行顺序异常导致的约束失效问题。
考虑到表中c_TermYear、c_TermSeason、c_DayOfClass三个字段允许为NULL，可以创建过滤唯一索引：

CREATE UNIQUE INDEX ux_Instructor_Class_Schedule
ON Class(c_InstructorID, c_TermYear, c_TermSeason, c_DayOfClass, c_TimeOfClass)
WHERE c_TermYear IS NOT NULL 
  AND c_TermSeason IS NOT NULL 
  AND c_DayOfClass IS NOT NULL;

索引创建后，数据库会在写入时自动校验约束，出现冲突直接抛出唯一键冲突错误，执行效率远高于触发器。

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