SQL Server Delete触发器更新同一个表中的多个列

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.

在“患者”表中引入外键约束“FK_Patients_Users_Deleted”可能导致循环或多个级联路径。在删除无操作或更新无操作时指定，或修改其他外键约束。

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?

编辑:在所有的ModifiedByID、CreatedByID、DeletedByID列上都有索引，这样好吗?删除用户当然是很少见的，因此是否值得在3列中添加索引?

3 个解决方案

#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)

#2

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.

注意，它正确地处理多行删除。现在还不清楚你贴的是什么，你现在的触发器能不能。

#3

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.

我真的怀疑这是否会更好。

#1