在使用transact-SQL删除触发器之前
问题描述:
我需要阻止删除职业为'程序员'的用户。 因此,数据库表结构是:在使用transact-SQL删除触发器之前
TABLE Client
Id | Name | SSN | Occupation | Birthdate |
我到目前为止创建触发看起来是这样的:
CREATE TRIGGER Check_User_Occupation
ON dbo.Client
FOR DELETE
AS
BEGIN
IF(SELECT c.Occupation
FROM deleted AS d
INNER JOIN Client as c ON d.Id = c.Id) = 'Programmer'
BEGIN
PRINT 'YES';
ROLLBACK TRAN
RAISEERROR(SELECT 'Could not delete client because he is a programmer');
END
END
GO
我想它可以比之前该行被删除,但我不不知道解决这个问题的正确方法。
任何帮助表示赞赏!
答
正确的语法是在这里:
(你不需要加入删除表,表本身,在删除的是整排的delete语句......)但是要注意一个事实,那你是不是能够做到在这种情况下批量删除。它只能用于行删除。
CREATE TRIGGER Check_User_Occupation
ON dbo.Client
FOR DELETE
AS
BEGIN
IF(SELECT Occupation FROM deleted) = 'Programmer'
BEGIN
PRINT 'YES';
ROLLBACK
RAISERROR ('Could not delete client because he is a programmer', -- Message text
16, -- Severity
1 -- State
)
END
END
GO
对于批处理命令,最好使用有如下语句:
IF(SELECT COUNT(*) FROM deleted WHERE Occupation = 'Programmer') > 0
但OFC如果你想加入这个表,你需要使用
INSTEAD OF DELETE
而在这你不需要使用ROLLBACK
Finnaly它应该看起来像:
CREATE TRIGGER Check_User_Occupation
ON dbo.Client
INSTEAD OF DELETE
AS
BEGIN
IF(SELECT Occupation FROM deleted) = 'Programmer'
BEGIN
PRINT 'YES';
RAISERROR ('Could not delete client because he is a programmer', -- Message text
16, -- Severity
1 -- State
)
END
END
GO
使用而不是删除触发器。 –