I have a very simple datamodel that represents a tree structure:

The RootEntity is the root of such a tree, it can contain children of type ContainerEntity and of type AtomEntity. The type ContainerEntity again can contain children of type ContainerEntity and of type AtomEntity but can not contain children of type RootEntity. Children are referenced in a well known order. The DB model for this is below.

My problem now is that when I delete a RootEntity I want all children to be deleted recursively. I have create foreign key with CASCADE DELETE and two delete triggers for this. But it is not deleting everything, it always leaves some items in the ContainerEntity, AtomEntity, ContainerEntity_Children and AtomEntity_Children tables. Seemling beginning with the recursionlevel of 3.

CREATE TABLE RootEntity
(
    Id UNIQUEIDENTIFIER NOT NULL,
    Name VARCHAR(500) NOT NULL,

    CONSTRAINT PK_RootEntity PRIMARY KEY NONCLUSTERED (Id), 
);

CREATE TABLE ContainerEntity
(
    Id UNIQUEIDENTIFIER NOT NULL,
    Name VARCHAR(500) NOT NULL,

    CONSTRAINT PK_ContainerEntity PRIMARY KEY NONCLUSTERED (Id),    
);

CREATE TABLE AtomEntity
(
    Id UNIQUEIDENTIFIER NOT NULL,
    Name VARCHAR(500) NOT NULL,

    CONSTRAINT PK_AtomEntity PRIMARY KEY NONCLUSTERED (Id), 
);

CREATE TABLE RootEntity_Children
(
    ParentId UNIQUEIDENTIFIER NOT NULL,
    OrderIndex INT NOT NULL,
    ChildContainerEntityId UNIQUEIDENTIFIER NULL,
    ChildAtomEntityId UNIQUEIDENTIFIER NULL,
    ChildIsContainerEntity BIT NOT NULL,

    CONSTRAINT PK_RootEntity_Children 
        PRIMARY KEY NONCLUSTERED (ParentId, OrderIndex),

    -- foreign key to parent RootEntity
    CONSTRAINT FK_RootEntiry_Children__RootEntity
        FOREIGN KEY (ParentId) REFERENCES RootEntity (Id)
        ON DELETE CASCADE,

    -- foreign key to referenced (child) ContainerEntity    
    CONSTRAINT FK_RootEntiry_Children__ContainerEntity
        FOREIGN KEY (ChildContainerEntityId) REFERENCES ContainerEntity (Id)
        ON DELETE CASCADE,

    -- foreign key to referenced (child) AtomEntity
    CONSTRAINT FK_RootEntiry_Children__AtomEntity
        FOREIGN KEY (ChildAtomEntityId) REFERENCES AtomEntity (Id)
        ON DELETE CASCADE,
);

CREATE TABLE ContainerEntity_Children
(
    ParentId UNIQUEIDENTIFIER NOT NULL,
    OrderIndex INT NOT NULL,
    ChildContainerEntityId UNIQUEIDENTIFIER NULL,
    ChildAtomEntityId UNIQUEIDENTIFIER NULL,
    ChildIsContainerEntity BIT NOT NULL,

    CONSTRAINT PK_ContainerEntity_Children 
        PRIMARY KEY NONCLUSTERED (ParentId, OrderIndex),

    -- foreign key to parent ContainerEntity
    CONSTRAINT FK_ContainerEntity_Children__RootEntity
        FOREIGN KEY (ParentId) REFERENCES ContainerEntity (Id)
        ON DELETE CASCADE,

    -- foreign key to referenced (child) ContainerEntity    
    CONSTRAINT FK_ContainerEntity_Children__ContainerEntity
        FOREIGN KEY (ChildContainerEntityId) REFERENCES ContainerEntity (Id)
        ON DELETE CASCADE,

    -- foreign key to referenced (child) AtomEntity
    CONSTRAINT FK_ContainerEntity_Children__AtomEntity
        FOREIGN KEY (ChildAtomEntityId) REFERENCES AtomEntity (Id)
        ON DELETE CASCADE,
);

CREATE TRIGGER Delete_RootEntity_Children ON RootEntity_Children FOR DELETE
AS
DELETE FROM ContainerEntity WHERE Id IN (SELECT ChildContainerEntityId FROM deleted)
DELETE FROM AtomEntity WHERE Id IN (SELECT ChildAtomEntityId FROM deleted)
GO

CREATE TRIGGER Delete_ContainerEntiy_Children ON ContainerEntity_Children FOR DELETE
AS
DELETE FROM ContainerEntity WHERE Id IN (SELECT ChildContainerEntityId FROM deleted)
DELETE FROM AtomEntity WHERE Id IN (SELECT ChildAtomEntityId FROM deleted)
GO