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.
BarID (FK to table Bar)
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.
By newphpcoder in forum Database
Last Post: 09-29-2010, 08:31 PM
By dhaya in forum Database
Last Post: 08-25-2003, 06:24 PM
By myLittleTools.net in forum web.announcements
Last Post: 03-10-2001, 01:35 PM
By Julian Pickard in forum VB Classic
Last Post: 02-09-2001, 09:42 PM
By Devaraj in forum Enterprise
Last Post: 05-11-2000, 01:48 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL