SQL Server Delete Trigger To Update Multiple Columns in Same Table

Question

I have the following trigger:

CREATE TRIGGER Users_Delete
   ON  Users 
   AFTER DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Patients
    UPDATE Patients SET ModifiedByID=NULL WHERE ModifiedByID=ID;
    UPDATE Patients SET CreatedByID=NULL WHERE CreatedByID=ID;
    UPDATE Patients SET DeletedByID=NULL WHERE DeletedByID=ID;

END

I was wondering if there's a way to "combine" those three UPDATE statements into something that would be like the following:

UPDATE Patients SET 
(ModifiedByID=NULL WHERE ModifiedByID=ID) OR 
(CreatedByID=NULL WHERE CreatedByID=ID) OR 
(DeletedByID=NULL WHERE DeletedByID=ID);

I'd really like to have only one statement to increase performance.

The reason I'm using the trigger instead on ON DELETE for the FOREIGN KEY is because I'm getting the error that having more than one ON DELETE causes the following error:

Introducing FOREIGN KEY constraint 'FK_Patients_Users_Deleted' on table 'Patients' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

EDIT: would it be good to have indexes on all of the ModifiedByID, CreatedByID, DeletedByID columns? Deleting a User will of course be rare, so is it worth adding indexes to 3 columns?

Answer 1

Something like this?

UPDATE Patients SET 
ModifiedByID = CASE WHEN ModifiedByID=ID THEN Null ELSE ModifiedById END,
CreatedByID = CASE WHEN CreatedByID=ID THEN Null ELSE CreatedById END,
DeletedByID = CASE WHEN DeletedByID=ID THEN Null ELSE DeletedById END
WHERE (ModifiedByID = ID OR CreatedByID = ID OR DeletedByID = ID)

Answer 2

You can do it in one statement, but that's not necessarily better for performance.

UPDATE
    Patients
SET
    ModifiedByID = CASE WHEN ModifiedID = ID THEN NULL ELSE ModifiedID,
    CreatedByID = CASE WHEN CreatedByID = ID THEN NULL ELSE CreatedByID,
    DeletedByID = CASE WHEN DeletedByID = ID THEN NULL ELSE DeletedByID

I really doubt that this will perform better though.

Answer 3

Regarding your original question.

Not really. I can't come up with anything better than

UPDATE Patients 
SET ModifiedByID= CASE WHEN ModifiedByID=ID THEN NULL ELSE ModifiedByID END,
CreatedByID= CASE WHEN CreatedByID=ID THEN NULL ELSE CreatedByID END,
DeletedByID= CASE WHEN DeletedByID=ID THEN NULL ELSE DeletedByID END
WHERE
ModifiedByID  IN (SELECT ID FROM DELETED)
OR 
CreatedByID  IN (SELECT ID FROM DELETED)
OR
DeletedByID  IN (SELECT ID FROM DELETED)

Note that this handles a multi row delete correctly. It is unclear from what you posted whether your current trigger does.