不插入成员的触发器不起作用 [英] trigger for not inserting members doesnt work

问题描述

我有这张桌子

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 为 1 或 2 或 3 .

and I want to create a trigger that if someone wants to insert a member which has a dep_id of 1 or 2 or 3.

并且 teacher_id 与 NULL 不同(因为 teacher_id 列中填充了 NULL 或另一个成员的ID)

And the teacher_id is different than NULL (as the teacher_id column is filled with either NULL or an id of another member)

我想到了这个

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 和 teacher_id 7 的成员(例如)它将被注册

but after all if I try to insert a member with dep_id 1 and teacher_id 7(for example) it will be registered

推荐答案

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

You don't need a trigger for this. A check constraint is sufficient:

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 不为空.您可能会发现逻辑更容易遵循:

Specifically, this ensures that when dep_id is in one of those departments, then the teacher_id is not null. You might find the logic easier to follow as:

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

这篇关于不插入成员的触发器不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！