不插入成员的触发器不起作用

我有这张桌子

CREATE TABLE members
(
    member_id INT PRIMARY KEY NOT NULL,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    web_page VARCHAR(200),
    e_mail VARCHAR(200),
    cv VARCHAR(800),
    dep_id INT,
    teacher_id INT
);

我想创建一个触发器，如果​​有人想插入一个具有dep_id of 1 or 2 or 3.

And the teacher_id不同于NULL（如teacher_id列填充有NULL或其他会员的 ID）

我想出了这个

CREATE TRIGGER employee_insup1 
ON members
FOR INSERT, UPDATE
AS
    DECLARE @dep_id INT, @teacher_id INT

    SELECT @dep_id = i.dep_id, @teacher_id = i.teacher_id
    FROM inserted i
  
    IF ((@dep_id = 1) AND (@teacher_id != NULL))
    BEGIN
        RAISERROR('Teacher_id expects NULL',16,1)
        ROLLBACK TRANSACTION
    END

但毕竟如果我尝试插入一个成员dep_id 1 and teacher_id 7（例如）它将被注册

您不需要为此触发。检查约束就足够了：

alter table members add constraint chk_members_dep_teacher
    check (dep_id not in (1, 2, 3) or teacher_id is not null);

具体来说，这确保了当dep_id属于这些部门之一，那么teacher_id不为空。您可能会发现逻辑更容易遵循：

alter table members add constraint chk_members_dep_teacher
    check (not (dep_id nt in (1, 2, 3) and teacher_id is null) );