我在一个表上有一个删除触发器,该触发器用于从另一个数据库中的表中删除条目。
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删除触发器
发布评论