SQL Server删除触发器-行句柄引用已删除的行或标记为删除的行 [英] SQL Server delete trigger - row handle referred to a deleted row or a row marked for deletion

问题描述

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

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.

这篇关于SQL Server删除触发器-行句柄引用已删除的行或标记为删除的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！