SQL Server删除触发器

本文介绍了SQL Server删除触发器-行句柄引用已删除的行或标记为删除的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在一个表上有一个删除触发器，该触发器用于从另一个数据库中的表中删除条目。

I have a delete trigger on a table which is used to delete entries from a table in an another database.

CREATE TRIGGER [dbo].[Trigger_Contracts_Delete] ON [dbo].[Contracts] AFTER DELETE NOT FOR REPLICATION AS IF @@ROWCOUNT = 0 RETURN DELETE seconddb.dbo.second_table WHERE contractId IN (SELECT d.ContractID FROM deleted d)

当我使用我的应用程序（旧版应用程序不了解其内部信息）删除记录时，出现错误引用了行句柄到已删除的行或标记为要删除的行

When I delete a record using my application (legacy app nothing know about its internals) I get the error "Row handle referred to a deleted row or a row marked for deletion"

但是，当我修改此触发器以添加附加的select语句（ SELECT d.ContractID FROM删除之前删除了d ）。我没有收到错误消息。添加select语句时，它起作用的原因可能是什么，是否通过在 deleted上发出select来锁定了 deleted表？ 会发出

However when I modified this trigger to add an additional select statement (SELECT d.ContractID FROM deleted d) just before delete.. I do not get the error message. What could be the reason for it to work when select statement is added, is it that by issuing select on "deleted" I have locked the "deleted" table ? it be issuing a

CREATE TRIGGER [dbo].[Trigger_Contracts_Delete] ON [dbo].[Contracts] AFTER DELETE NOT FOR REPLICATION AS IF @@ROWCOUNT = 0 RETURN SELECT d.ContractID FROM deleted d; DELETE seconddb.dbo.second_table WHERE contractId IN (SELECT d.ContractID FROM deleted d)

推荐答案

您的表缺少主键！ 添加主键，问题将消失。

your table(s) are missing primary keys! add primary keys and the problem will go away.