SQL Server删除触发器更新同一个表中的多个列
我有以下触发器:
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
我想知道是否有一种方法可以将这三个 UPDATE 语句“组合”成如下所示的内容:
UPDATE Patients SET
(ModifiedByID=NULL WHERE ModifiedByID=ID) OR
(CreatedByID=NULL WHERE CreatedByID=ID) OR
(DeletedByID=NULL WHERE DeletedByID=ID);
我真的希望只有一个语句来提高性能。
我在 ON DELETE
上使用触发器来代替 FOREIGN KEY
的原因是因为我收到了一个错误,指出有多个 ON DELETE 导致以下错误:
在表“Patients”上引入外键约束“FK_Patients_Users_Deleted”可能会 导致循环或多级联路径。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其他 FOREIGN KEY 约束。
编辑:在所有 ModifiedByID、CreatedByID、DeletedByID 列上建立索引会好吗?删除用户的情况当然很少见,那么是否值得为 3 列添加索引呢?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
关于你原来的问题。
并不真地。我想不出比
请注意这可以正确处理多行删除更好的方法了。从您发布的内容来看,尚不清楚您当前的触发器是否有效。
Regarding your original question.
Not really. I can't come up with anything better than
Note that this handles a multi row delete correctly. It is unclear from what you posted whether your current trigger does.
像这样的东西吗?
Something like this?
您可以在一条语句中完成此操作,但这不一定对性能更好。
我真的怀疑这会表现得更好。
You can do it in one statement, but that's not necessarily better for performance.
I really doubt that this will perform better though.