用于删除的 MSsql 触发器

Question

我希望触发器阻止从角色表中删除该员工，如果该员工已经在处理任务。如果任务尚未启动，则应能够从角色表中删除该员工。

触发：

CREATE TRIGGER trg_role_del
ON Role
FOR DELETE AS
IF EXISTS (SELECT Task.StartTask, Role.EmployeeId,
FROM Task, Role, deleted
WHERE Role.EmployeeId = deleted.EmployeeId
AND Role.EmployeeId = Task.EmployeeId
AND GETDATE() > Task.StartTask)
BEGIN
    RAISERROR ('This role is working on a task, cannot be removed', 16, 1)
ROLLBACK TRANSACTION
END;

删除语句：

DELETE FROM Role
WHERE EmplyeeId = '003'

但是，上面的触发器不起作用。所有删除都会完成，触发器不会停止任何内容。如果 SELECT 语句本身单独运行，它确实从表中选择了正确的值，因此 SELECT 似乎没问题。我似乎无法让代码正常工作。帮助将不胜感激！

Answer 1

我会使用而不是删除触发器：

CREATE TRIGGER trg_role_del
ON Role
INSTEAD OF DELETE AS
DELETE r
FROM Role r
INNER JOIN deleted ON r.EmployeeId = deleted.EmployeeId
LEFT JOIN Task ON r.EmployeeId = Task.EmployeeId
               AND GETDATE() > Task.StartTask
WHERE Task.Id IS NULL -- Any non-nullable column on the task table is good here

在 rextester 上观看现场演示

Answer 2

您创建了一个"删除后"触发器，而您应该创建一个"INSTEAD OF"触发器。