Storing and deleting data recursively in SQL Server
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
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
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)
精彩评论