SQL Server触发器，用于在级联上进行递归删除 [英] SQL Server Trigger for recursive delete on a cascade

问题描述

我有一个表，其中包含类似树的结构

I have a table which contains a tree like structure

它的形式为：

- nodeID，fkID，parentNode

-nodeID,fkID,parentNode

（如果parentNode是根节点，则parentNode为NULL；如果是子节点，则其父节点的nodeID）；
（如果为root，则fkID为NULL

(parentNode is NULL if it is the root and the nodeID of its parent if it is a child) (fkID is NULL if it is not the root)

fkID是一个FK，当在另一个表中删除时，它会将其删除级联到该表。但是，此级联删除仅引用根节点。数据库上还有另一个约束，其中除非先删除其子节点，否则不能删除根节点。但是我无法对自引用约束进行级联，因为SQL SERVER没有提供该选项。我以为触发器可能是一个很好的解决方案，但是我必须先递归递归树，然后在父母之前删除孩子。这需要我在级联发生之前删除。有什么好方法吗？

the fkID is a FK which when deleted in another table cascades its delete to this table. This cascading delete only however references the root node. There is another constraint put on the database in which a root node cannot be deleted unless its children are deleted first. However I can't cascade on the self referencing constraint because SQL SERVER dosen't give me that option. I thought a trigger might be a good solution however I have to recurse down the tree first and delete the children before the parents. which would require me to delete before the cascade happens. Is there a good way to do this?

我在以下触发器上遇到了以下错误

I've gotten the following error on the following trigger

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.deleteChildren
ON  dbo.faultTreeNodes
INSTEAD OF DELETE
AS 
BEGIN
SET NOCOUNT ON;
   -- Insert statemets for trigger here
END
GO

错误：

CANNOT CREATE TRIGGER INSTEAD OF DELETE BECAUSE THIS 
TABLE HAS A FOREIGN KEY WITH A CASCADING DELETE

谢谢您的任何建议或帮助！

Thank you in advance for any advice or help!

推荐答案

即使我已投票关闭了重复，我想我要发布一个答案，因为再三考虑，重复问题的答案在问题编辑后最终变得有些混乱，并且最近添加的语句

Even though I have voted to close as duplicate, I think I'm going to post an answer, because, on a second thought, the duplicate question's answer ended up being a little bit confusing after the question edits, and the lately added statement that

我想您只需要从类别的递归外键中删除该ON DELETE CASCADE标志即可。 CAT_SCH的外键上的CASCADE标志应该没关系

I guess you just need to drop that ON DELETE CASCADE flag from your recursive foreign key in Categories. The CASCADE flag on the foreign key from CAT_SCH should not matter

实际上不是真的（SQL Server会在删除时引发错误，因为在一个字段上的级联将与在另一个字段上的不采取行动相冲突）。

is actually not true (SQL Server will raise an error on a delete, because cascade on one field will conflict with no action on another field).

主要要点仍然存在：

• 在不执行任何删除操作时，您将两个外键都声明为 。


• 您在 main表上创建一个而不是delete 触发器，该触发器将删除必需的子级，然后删除主记录本身。

• You declare both foreign keys as on delete no action.
• You create an instead of delete trigger on the "main" table that will delete required children, in order, and then delete the "main" record itself.

例如（ SQL小提琴）：

create table main(id int not null primary key);

create table nodes (
  nodeID int not null primary key,
  fkID int null foreign key references main(id),
  parentID int null foreign key references nodes(nodeID)
);

create trigger dlt on main
instead of delete
as
begin

  declare @to_delete table (nodeID int not null, level int not null, primary key(level, nodeID));

  begin tran;

  with cte as (
    select n.nodeID, 0 as level
    from nodes n inner join deleted d on n.fkID = d.id

    union all

    select n.nodeID, level + 1
    from nodes n inner join cte c on n.parentID = c.nodeID
  )
  insert into @to_delete(nodeID, level)
  select nodeID, level
  from cte;

  declare cur cursor
  local
  forward_only
  read_only
  for
    select distinct level from @to_delete order by level desc;

  open cur;

  declare @cur_level int;

  fetch next from cur into @cur_level;
  while @@fetch_status = 0
  begin
    delete from nodes
    from nodes n inner join @to_delete d on n.nodeID = d.nodeID
    where d.level = @cur_level;

    fetch next from cur into @cur_level;
  end;


  close cur;
  deallocate cur;

  delete from main from main m inner join deleted d on m.id = d.id;

  commit tran;
end;

这篇关于SQL Server触发器，用于在级联上进行递归删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！