How am I wrong in 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 is my details:

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 execute this query:

delete from Node where ID=534

And this is the resulting dataset:

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 a trigger not trigger the trigger to execute recursively until all deleted records are deleted?

EDIT: Please note that I posted a working solution below, but marked the other answer as correct, since my question was not “what is the solution,” but rather “why the way I do it doesn't work.”

Answer 1

. , 32 .

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Answer 2

, . :

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: , , .

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

Answer 3

( Allow Triggers to Fire Others True, sql)

sql screenshot http://img34.imageshack.us/img34/5259/capturehjf.png

Answer 4

534.

-
CTE, .

Answer 5

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

This can be solved by writing an INSTEAD OF DELETE trigger , as described in my blog.