How am I misunderstanding trigger recursion in TSQL?

Question

I have the following table:

if object_id(N'dbo.Node') is null
create table dbo.Node
(
    ID bigint identity primary key,
    ParentID bigint null, -- references Node(ID)
    DateCreated datetime not null,
    LastUpdated datetime not null,
    [Name] nvarchar(500) not null,
);

I have this trigger to achieve cascading deletes within the same table:

create trigger Node_Delete on Node for delete
as
begin
    delete from Node where ParentID in (select id from deleted)
end

Here's my data set:

ID                   ParentID             DateCreated             LastUpdated             Name
534                  514                  2010-01-12 10:15:03.940 2010-01-12 10:15:03.940 Test 1
535                  534                  2010-01-12 10:15:08.563 2010-01-12 10:15:08.563 Test 2
536                  535                  2010-01-12 10:15:12.063 2010-01-12 10:15:12.063 Test 3
537                  536                  2010-01-12 10:15:18.510 2010-01-12 10:15:18.510 Test 4

Now I perform this query:

delete from Node where ID=534

And this is the resultant data set:

ID                   ParentID             DateCreated             LastUpdated             Name
536                  535                  2010-01-12 10:15:12.063 2010-01-12 10:15:12.063 Test 3
537                  536                  2010-01-12 10:15:18.510 2010-01-12 10:15:18.510 Test 4

Why does the DELETE statement in the trigger not cause a recursive execution of the trigger until all descended records are deleted?

EDIT: Note that I have posted a working solution below, but marked a different answer as correct, seeing as my question was not "what is the solution", but rather "why does the way I'm doing it not work".

Answer 1

because the deleted pseudotable has only one row with id 534.

you could add a cascade delete on the parent-child relationship
or use a CTE to delete everything.

Answer 2

You have to enable recursive triggers for the database. Notice that you can get only 32 levels deeper.

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Answer 3

The FOR DELETE trigger is fired after the delete operation. Thus any child records violate the FK constraint, making the deletion impossible.

You can solve this by writing an INSTEAD OF DELETE trigger as decribed on my blog.

Answer 4

Turn on nested triggers (or set the Allow Triggers to Fire Others option to True, depending on your sql version)

Answer 5

I modified my trigger to cascade to an unlimited depth without turning on trigger recursion. Here is my solution:

create trigger Node_Delete on Node instead of delete
as
begin
    create table #del ( id bigint, depth int )
    declare @depth int
    set @depth = 1
    insert into #del select id, @depth from deleted
    while @@rowcount > 0
    begin
        set @depth = @depth + 1
        insert into #del select id, @depth from Node where ParentID in (select id from #del where depth = @depth-1)
    end
    delete from Node where ID in (select id from #del)
end

EDIT: I've now settled on an even better solution using common table expressions, as suggested in another answer by Mladen Prajdic below.

create trigger Node_Delete on Node instead of delete
as
begin
    with nodes
    as
    (
        select n.ID, n.ParentID, 1 as Level
        from Node n where n.ID in (select ID from deleted)
        union all
        select n.ID, n.ParentID, p.Level+1 as Level
        from Node n
        inner join nodes p on p.ID = n.ParentID
    )
    delete from Node where ID in (select ID from nodes);
end