CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Shyamal Parikh
Shyamal Parikh

Reputation: 3068

SQL: Delete all nested self referencing records

This is the structure of a table: Id Name ParentId

ParentId is a foreign key to primary column Id. Now lets say I have a few rows like: (Only showing ParentId from rows)

     01
   /    \
  5      2
        / \
       3   4

I have setup InsteadOfDelete trigger as seen below:

CREATE TRIGGER [dbo].[tr_tb] ON [dbo].[Some_tb]
    INSTEAD OF DELETE 
AS 
    /*Delete from another table*/
    DELETE FROM SomeOther_tb WHERE OtherId IN(SELECT Id FROM deleted);

    /*Delete childs from this table*/
    DELETE FROM Some_tb WHERE ParentId IN(SELECT Id FROM deleted);

    /*Delete from this table*/
    DELETE FROM Some_tb WHERE Id IN(SELECT Id FROM deleted);

When record with Id = 01 (check representation of childs above) is deleted. Only immediate childs (Id=5,2) are deleted leaving behind sub-child records (Id = 3,4).

I am assuming InsteadOfTrigger functions are not triggered for childs (Id=5,2).

How do I solve this issue, such that all the nested childrens are deleted?

Upvotes: 2

Views: 820

Answers (3)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

MS SQL Server 2008 supports Common Table Expressions (CTE) which are great for hierarchical data. And CTE may exist in trigger as well.

CREATE TRIGGER [dbo].[tr_tb] ON [dbo].[Some_tb]
    INSTEAD OF DELETE 
AS 
    /*Delete from another table*/
    ;with tbl as (--this is CTE
      --anchor query (top level)
      select t.id, t.parentid
      from someOther_tb t
      inner join deleted d on d.id = t.parentid
      union all
      --recursive query
      select t.id, t.parentid
      from someOther_tb t
      inner join tbl on tbl.id=t.parentid
    )
    delete someOther_tb
    where id in (select id from tbl)        

    --Now it is safe to delete from main table
    DELETE FROM Some_tb WHERE Id IN(SELECT Id FROM deleted)

Upvotes: 1

Shyamal Parikh
Shyamal Parikh

Reputation: 3068

DECLARE @DELETED TABLE(
        Id BIGINT
    );


WITH LoopCTE AS (
  SELECT Id, ParentId,0 AS steps
  FROM dbo.Some_tb
  WHERE Id IN (SELECT Id FROM deleted)

  UNION ALL

  SELECT mgr.Id, mgr.ParentId, parent.steps +1 AS steps
  FROM LoopCTE AS parent
    INNER JOIN dbo.Some_tb AS mgr
      ON parent.Id = mgr.ParentId
)
INSERT INTO @DELETED SELECT Id FROM LoopCTE AS u;

/*Delete from another table*/
DELETE FROM SomeOther_tb WHERE OtherId IN(SELECT Id FROM @DELETED);


/*Delete childs from this table*/  <-- No longer required
/*DELETE FROM Some_tb WHERE ParentId IN(SELECT Id FROM @DELETED);*/

/*Delete from this table*/
DELETE FROM Some_tb WHERE Id IN(SELECT Id FROM @DELETED);

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

CREATE TRIGGER [dbo].[tr_tb] ON [dbo].[Some_tb]
    INSTEAD OF DELETE 
AS 
    DECLARE @DELETED TABLE(
        ID BIGINT
    )

    INSERT INTO @DELETED
        SELECT Id FROM deleted

    --could use a CTE here as well for better performance
    WHILE @@ROWCOUNT>0
        BEGIN
            INSERT INTO @DELETED
                SELECT Id
                FROM Some_tb t
                inner join @DELETED d on d.parentid=t.id
        END

    /*Delete from another table*/
    DELETE FROM SomeOther_tb WHERE OtherId IN(SELECT Id FROM @DELETED);

    /*Delete childs from this table*/
    DELETE FROM Some_tb WHERE ParentId IN(
       SELECT Id FROM @DELETED WHERE ID NOT IN (SELECT ID FROM DELETED)
    );

    /*Delete from this table*/
    DELETE FROM Some_tb WHERE Id IN(SELECT Id FROM DELETED);

Upvotes: 2

Related Questions