SQL Server 2005 Multi-table Hierarchical Delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Server 2005 Multi-table Hierarchical Delete

Author

Topic

gmarut
Starting Member

8 Posts

Posted - 2010-10-13 : 15:06:18

I have 2 tables "Foo" and "Bar". Both of these tables are used to represent a tree hierarchy.

Table Foo:
FooID (PK)
BarID (FK to table Bar)

Table Bar:
BarID (PK)
FooID (FK to table Foo)

Foo to Bar is a one-to-many relationship on Foo.BarID -> Bar.BarID and Bar to Foo is also a one-to-many relationship on Bar.FooID -> Foo.FooID (the business logic requires these two tables to be split up and not referenced in the same table). Since these are recursive relationships, the ON DELETE and ON UPDATE actions must be set to NO ACTION. What is the best way to go about trying to delete a record when it can have multiple subtrees which in turn can have multiple subtrees... and so on?

Also, these data sets are pretty large, a subtree can extend as far as thousands or even potentially millions or subtrees therefore using nested delete triggers (with a max of 32 recursions) will not work.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 15:54:36

do u know which records need to be deleted, or will they be determined based on the relationships?

gmarut
Starting Member

8 Posts

Posted - 2010-10-13 : 17:14:11

Ill know which node I need to delete and then from there when I pull a node out, it needs to go through and purge all subtrees as to make sure there are no orphaned child trees.

For example, I know that I need to delete a Foo object with FooID of 287 (arbitrary number). FooID 287 could have X number of subtrees with each of those subtrees having N number of subtrees and so on... I need to make sure everything below FooID 287 also gets deleted.

gmarut
Starting Member

8 Posts

Posted - 2010-10-13 : 18:35:01

Phew! I figured it out without using a cursor.

Basically I allowed both of the FK's to have null values, used CTE to grab a list of all the IDs in a subtree starting at a specific node and then updated those IDs to null before running a delete statement. Trying to delete rows before nulling out the foreign keys will just throw an exception.

I had a hard time finding a good solution online to this so I provided the code that I came up with.. hopefully someone else can benefit from this.

-- =============================================
-- Author: Greg Marut
-- Create date: 10/13/2010
-- Description: Uses recursion to delete a Bar and all subtrees extending off of this Bar
-- =============================================
CREATE PROCEDURE sp_DeleteTree
@pBarID int
AS
BEGIN
-- Recursively traverse the tree and list all Bars/Foos that extend off of this Bar
WITH CTE (FooID, BarID)
AS
(
-- Set up the initial values
SELECT FooID, BarID
FROM Foo
WHERE BarID = @pBarID

UNION ALL
-- Recursive select statement to traverse the sub trees
SELECT Foo.FooID, Foo.BarID
FROM CTE
INNER JOIN Bar ON (CTE.FooID = Bar.FooID)
INNER JOIN Foo ON (Bar.BarID = Foo.BarID)
)

-- Insert the results into a temporary table
SELECT * INTO #ttBarTree FROM CTE;

-- Begin a try block
BEGIN TRY
-- Start a new transaction
BEGIN TRANSACTION

-- break the tree link for all foos that need to be deleted
UPDATE Foo
SET BarID = NULL
WHERE BarID IN (SELECT DISTINCT BarID FROM #ttBarTree)

-- break the tree link for all bars that need to be deleted
UPDATE Bar
SET FooID = NULL
WHERE BarID IN (SELECT DISTINCT BarID FROM #ttBarTree)

-- Delete all the affected foos
DELETE FROM Foo
WHERE FooID IN (SELECT DISTINCT FooID FROM #ttBarTree)

-- Delete all the affected bars
DELETE FROM Bar
WHERE BarID IN (SELECT DISTINCT BarID FROM #ttBarTree)

--Commit the transaction
COMMIT
END TRY
BEGIN CATCH
--Rollback the transaction
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

-- Raise the error
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

-- Drop the temporary table
DROP TABLE #ttBarTree;
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources