sql-server - 如何在 SQL Server 中触发器的 if 和 else 条件下插入表

Question

create trigger thequery4 
on employees
instead of insert, delete
as
    declare @var3 int

    select @var3 = d.D_ID  
    from inserted d, DEPARTMENTS dp 
    where d.D_ID = dp.D_ID

    if (@var3 is null)
    begin
        print 'you are NOT  allowed , B/C IS Its d_Id is not present in department '
    end
    else
    begin
        ---- insert ----
    end

标签： sql-servertriggers

Answer 1

就像 Martin Smith 在评论部分中建议的那样，您确实需要两个表之间的外键约束。但是，处理此问题的触发器看起来像......

CREATE TRIGGER thequery4 
ON employees
INSTEAD OF INSERT, DELETE
AS
BEGIN

    IF EXISTS ( SELECT 1 FROM inserted i
                WHERE NOT EXISTS (  SELECT 1 
                                    FROM DEPARTMENTS dp WHERE  i.D_ID = dp.D_ID))
        BEGIN
            PRINT 'you are NOT  allowed , B/C IS Its d_Id is not present in department '
        END
    ELSE
        BEGIN
            ---- insert ----
        END
END