HiI'm working on a content management system in ASP.NET with C# codebehinds, which can have many sections - sections are stored in a table called "Section". Each section can have many child-sections; each section can be the child-section of up to 1 section. Thus there is a loop foreign key relationship acting on the table (apologies if I get the terminology wrong), from "Section.ID" to "Section.ParentID". I think of it as a many-branching tree.When the user wants to delete a section, I need for it to be possible to delete all of the child-sections of the selected section. There is a caveat, in that each section is represented in the system by a directory (specified by the "Section.Path" column) - the directory is for the resources (files) that that section of the CMS holds. Thus, when I delete a section from the database, not only do I want to delete all the section's children, grandchildren, and so on down the tree; I also want to keep a list of the sections I'm deleting, so that I can delete their on-disk directories.I'm having some difficulty achieving this.I firstly tried to achieve what I want (minus the "keeping a list of which sections got deleted" part) with a trigger on the "Section" table that reads like this:quote:
CREATE TRIGGER DeleteSubSections ON [dbo].[DocumentLibrary] INSTEAD OF DELETE ASDELETE FROM DocumentLibrary WHERE ParentID IN (SELECT ID FROM DELETED)DELETE FROM DocumentLibrary WHERE ID IN (SELECT ID FROM DELETED)
This (as I recall - it was some time ago that I tried, gave up for a bit, and came back to it) didn't work further than deleting a section's immediate children - grandchildren were immune. And in any case, it did not maintain for me a list of the sections being deleted. On top of that, it's (obviously) a trigger - a feature of SQL Server I haven't made use of at all, and am not yet familiar with.So, what I'm after, is a SQL stored procedure I can call once from my codebehind, which will delete in the database all the Sections that are children of the section to be deleted, as well as the section the user selected. The on-disk delete can be performed using the "Section.Path" in the selected section, since System.IO.Directory.Delete can be made to recurse down the directory tree as required. I may also, later on, want to allow the user to say "yes, delete this stuff from the DB, but leave it on the disk".I have included a cut and paste of my table at the end of this post. The first line consists of the table's column headings, subsequent lines represent each row in the table, with commas separating the fields (there are no commas in the data in this example). "Section.ID" is the primary key in the "Section" table, and is an identity.Can anyone help, please?ID, ParentID, SectionType, Path, Name, Description, RelatedProjects, ShowDescription, ShowRelatedProjects1,NULL,1,resources/sales/,Sales,All about Sales,Sales type stuff,1,12,NULL,1,resources/developers/,Developers,All about developers,foo,1,04,NULL,1,resources/client/,Client,All about clients,bar,0,15,1,2,resources/sales/sales figures/,Sales figures,Some sales charts,NULL,0,06,1,2,resources/sales/sales documents/,Sales documents,Some sales documents,NULL,0,07,2,1,resources/developers/developer resources/,Developer Resources,Some development resources,NULL,0,08,2,1,resources/developers/old code libraries/,Old code libraries,Some old code libraries,NULL,0,09,2,2,resources/developers/developer morale/,Developer morale,Some comics,NULL,0,038,NULL,1,~/resources/foo/,foo,,,0,041,38,1,~/resources/foo/1/,1,,,0,042,41,1,~/resources/foo/1/2/,2,,,0,043,42,1,~/resources/foo/1/2/3/,3,,,0,044,38,1,~/resources/foo/1.2/,1.2,,,0,0 |
Help with SQL query; return child nodes of parent
Posted - 2003-08-12 : 11:10:06