SQL Server 2008 - How to update a field with DELETE trigger

Question

I want to update a field in the same table that the record is being deleted from but not too sure how. The table is pretty simple and has a self reference on it:

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

Here's my trigger:

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

So if I delete Jane (UserID = 2), I'd like the trigger to update any record with the EmailUserID of 2. How can I accomplish this?

Answer 1

You may use AFTER DELETE Trigger:

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

Answer 2

Note that if you set up the table with a proper FOREIGN KEY constraint, an after trigger is not going to do the job. The DELETE happens logically before the operations in the trigger, so you will get:

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

So if you do want data integrity so that nobody can stuff garbage into the EMailUserID column, you can use an INSTEAD OF trigger as follows:

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

Sample try create a copy of dbo.Users, as above, in tempdb, then run this):

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