SQL Server 2008 - 如何使用DELETE触发器更新字段

Question

我想更新记录正在从中删除的同一个表中的字段，但不太确定如何。该表是非常简单，上有一个自我参考：

UserID FName EmailAddress EmailUserID 
1   Frank [email protected] 2 
2   Jane [email protected] NULL 
3   John [email protected] 1 
4   Brett [email protected] 2 

这里是我的触发器：

CREATE TRIGGER [dbo].[CAT_DeleteUser] 
    ON Users 
    AFTER DELETE 
AS 
BEGIN 

    SET NOCOUNT ON; 

    UPDATE Users 
    SET EmailUserID = NULL 
    WHERE EmailUserID = ID_OF_DELETED_USER <-- don't know how to get this 

END 

所以，如果我删简（用户ID = 2），我想触发更新EmailUserID为2的任何记录。我该如何完成此操作？

Answer 1

请注意，如果您用适当的FOREIGN KEY约束设置表，后触发器不会完成这项工作。该DELETE逻辑发生在触发的操作之前，所以你会得到：

Msg 547, Level 16, State 0, Line 2 
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint ... 

所以，如果你想要的数据完整性，使任何人都无法东西垃圾到EMailUserID列，您可以使用INSTEAD OF触发如下：

CREATE TABLE dbo.Users 
(
    UserID INT NOT NULL PRIMARY KEY, 
    FName NVARCHAR(32), 
    EmailAddress VARCHAR(320), 
    EmailUserID INT NULL FOREIGN KEY REFERENCES dbo.Users(UserID) 
); 
GO 

CREATE TRIGGER [dbo].[CAT_DeleteUser] 
ON dbo.Users 
INSTEAD OF DELETE 
AS 
BEGIN 
    SET NOCOUNT ON; 

    -- clean up references first 
    UPDATE u SET u.EmailUserID = NULL 
     FROM dbo.Users AS u 
     INNER JOIN deleted AS d 
     ON u.EmailUserID = d.UserID; 

    -- now delete the row 
    DELETE u 
    FROM dbo.Users AS u 
    INNER JOIN deleted AS d 
    ON u.UserID = d.UserID; 
END 
GO 

样品尝试创建dbo.Users副本，这样，在tempdb，然后运行这个）：

INSERT dbo.Users(UserID, FName, EmailAddress, EmailUserID) VALUES 
(1,'Frank','[email protected]',2 ), 
(2,'Jane ','[email protected] ',NULL), 
(3,'John ','[email protected] ',1 ), 
(4,'Brett','[email protected]',2 ); 

SELECT * FROM dbo.Users; 
GO 

DELETE dbo.Users WHERE UserID = 2; 
GO 

SELECT * FROM dbo.Users; 
GO 

DROP TABLE dbo.Users; 
GO 

Answer 2

对不起，得到了错误的问题在第一时间......在这种情况下AFTER触发器是可能的：

CREATE TRIGGER [dbo].[CAT_DeleteUser] 
    ON Users 
    AFTER DELETE 
AS 
BEGIN 

    SET NOCOUNT ON; 

    UPDATE Users 
    SET EmailUserID = NULL 
    WHERE EmailUserID IN (SELECT UserID FROM DELETED) 

END 

Answer 3

您可以使用DELETE AFTER触发器：

CREATE TRIGGER [dbo].[CAT_DeleteUser] ON Users 
    AFTER DELETE 
AS 
BEGIN 

    SET NOCOUNT ON; 

    UPDATE Users 
    SET EmailUserID = NULL 
    FROM Users 
    JOIN DELETED 
     ON Users.EmailUserID = Deleted.UserID 

END