Storing and deleting data recursively in SQL Server

Question

Hi,

I am creating a blog component for a client's website and I'm having a bit of a problem with the data. I have a simple "content" table which holds the content for a blog entry. I'm reusing this table for comments on blog entries as the fields are all the same.

I have added a ParentID to the content table so that I can link comments to their parent content item. In order to ensure I don't get zombied content items I have added a trigger to delete any child content items when a content item gets deleted:

delete from content where ParentID in (select ID from deleted)

However, when I delete content items that don't even have any child content items I am getting an error from SQL Server (2008):

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

The trigger should fire once for the deleted content but shouldn't be called again if the content has no children? Does anybody know why I am getting this error?

TIA

Answer 1

You better use the a set-based solution for that:

WITH    h AS
        (
        SELECT  id
        FROM    content
        WHERE   id = @row_to_delete
        UNION ALL
        SELECT  c.id
        FROM    h
        JOIN    content c
        ON      c.parentID = h.id
        )
DELETE
FROM    content
WHERE   id IN
        (
        SELECT  id
        FROM    h
        )

To make sure that no orphan rows are ever left, create a fake common ancestor with hardcoded id and parent equal to that id:

INSERT
INTO    content (id, parentid)
VALUES  (0, 0)

ALTER TABLE content
ADD CONSTRAINT fk_content_parent_self
FOREIGN KEY (parentId)
REFERENCES content (id)

Answer 2

Change the trigger to only perform the delete if rows exist

DECLARE @ID int
SELECT @ID = TOP 1 ID from content where ParentID in (select ID from deleted)
IF (@ID IS NOT NULL)
BEGIN
    delete from content where ParentID in (select ID from deleted)
END