sql-server - SQL Server 删除触发器 - 引用已删除行或标记为删除的行的行句柄

Question

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

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)

当我使用我的应用程序(遗留应用程序对其内部结构一无所知)删除记录时，我收到错误消息“行句柄引用了已删除的行或标记为删除的行”

但是，当我修改此触发器以在删除之前添加一个额外的选择语句(SELECT d.ContractID FROM deleted d)时，我没有收到错误消息。添加 select 语句时它起作用的原因可能是什么，是不是通过在“已删除”上发出选择我锁定了“已删除”表？它会发出一个

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)

Answer 1

您的表缺少主键!添加主键，问题就会消失。