TSQL级联删除子记录? [英] TSQL Cascade delete for child records?

问题描述

我有一个父/子表(简单的树)表结构(ID，ParentID)，我想在其中删除(并获取)给定父 ID 的所有孩子的 ID - 类似于这篇文章 sql server，级联删除和父/子表 .

I have a parent/child table (simple tree) table structure (ID, ParentID), where I want to delete (and get the ID of) all children for a given parent ID - similar to this post sql server, cascade delete and parent/child table .

在循环期间，我获得了当前 ID，我还将使用此 ID 执行其他操作.

During the loop, where I've got the current ID, I will also be performing other actions with this ID.

谁能给我一个 SPROC 的例子来完成这个?

Can someone give me an example of the SPROC to accomplish this?

谢谢

推荐答案

假设您使用的是 SQL SERVER 2005，以下是实现此目的的存储过程示例(通过使用 CTE):

Assuming, you're on SQL SERVER 2005, here is the example of stored procedure to accomplish this (by using CTE):

CREATE PROCEDURE [DeleteRecordWithChild]
    @id int
AS
BEGIN
    WITH Nodes ([Id], [ParentId], [Level]) 
    AS (
        SELECT  T.[Id], T.[ParentId], 0 AS [Level]
        FROM    [dbo].[YourTable] T
        WHERE   T.[Id] = @id

        UNION ALL

        SELECT  T.[Id], T.[ParentId], N.[Level] + 1
        FROM    [dbo].[YourTable] T
                INNER JOIN Nodes N ON N.[Id] = T.[ParentId]
    )

    DELETE
    FROM    [YourTable]
    OUTPUT  deleted.*
    WHERE   [Id] IN (
        SELECT  TOP 100 PERCENT N.[Id] 
        FROM    Nodes N
        ORDER BY N.[Level] DESC
    );
END

这会从表中删除由 @id 参数定义的行以及所有子节点，并在单个操作中将删除的值返回给处理应用程序.

This removes a row defined by @id parameter with all child nodes from the table and returns the deleted values to the processing application in a single action.

您也可以将删除的行返回到表变量中(应在 CTE 之前定义):

You also could return deleted rows into a table variable (should be defined before CTE):

DECLARE @deleted_rows TABLE
(
    [Id] int,
    [ParentId] int,
    [Level] int
);

然后

DELETE
FROM    [YourTable]
OUTPUT  deleted.* INTO @deleted_rows
WHERE   [Id] IN (
    SELECT  TOP 100 PERCENT N.[Id] 
    FROM    Nodes N
    ORDER BY N.[Level] DESC
);

这篇关于TSQL级联删除子记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！