SQL Server 2005 Multi-table Hierarchical Delete... need help


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: SQL Server 2005 Multi-table Hierarchical Delete... need help

  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unhappy SQL Server 2005 Multi-table Hierarchical Delete... need help

    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.

  2. #2
    Join Date
    Oct 2010
    Posts
    3
    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 SC.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 SC.Foo
    WHERE BarID = @pBarID

    UNION ALL
    -- Recursive select statement to traverse the sub trees
    SELECT Foo.FooID, Foo.BarID
    FROM CTE
    INNER JOIN SC.Bar ON (CTE.FooID = Bar.FooID)
    INNER JOIN SC.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 SC.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 SC.Bar
    SET FooID = NULL
    WHERE BarID IN (SELECT DISTINCT BarID FROM #ttBarTree)

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

    -- Delete all the affected bars
    DELETE FROM SC.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

Similar Threads

  1. Replies: 2
    Last Post: 09-29-2010, 08:31 PM
  2. query tuning
    By dhaya in forum Database
    Replies: 11
    Last Post: 08-25-2003, 06:24 PM
  3. myLittleAdmin SQL Server version
    By myLittleTools.net in forum web.announcements
    Replies: 0
    Last Post: 03-10-2001, 01:35 PM
  4. Replies: 1
    Last Post: 02-09-2001, 09:42 PM
  5. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 01:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center