August 8, 2003 at 9:31 am
I have a table "Folders" contains [fid], which is an int primary key, and [pid], which is an int. The [pid] corresponds to the [fid] if that folder is a child of the other folder... blah blah blah you get the idea. Self-referencing hierarchical setup.
Anyway, how can I write a stored procedure to delete not only one record, but ALL records that are children somewhere down the line to that record? So for example, if I delete a second level folder, it will delete all levels below that that eventually are related to that folder? I've tried triggers, but they only work one level deep (or I don't know how to set them up).
There is also one more part. I have a folder called "Items" which have a [pid] which correspond to the [fid] if its a child of a folder. I also need the SP to delete all of these items no matter how many levels deep they are.
Any ideas?
Thanks
August 8, 2003 at 12:20 pm
Write a trigger which deletes all rows which match on the pid. The corresponding deletes will recursively trigger more deletes, etc...
CREATE TRIGGER dbo.MyTable_OnDelete
ON MyTable
FOR DELETE
AS
DELETE FROM MyTable WHERE pid=deleted.fid
GO
August 11, 2003 at 8:49 am
The CREATE TRIGGER statement that jpipes offered doesn't work; it uses "deleted" incorrectly. Also, I don't think triggers can trigger themselves recursively. I experimented a little and couldn't get it to work.
Another great way to do this would be to use ON DELETE CASCADE, but SQL Server doesn't support that for self-referencing foreign keys.
What you need is (as you mentioned) a stored procedure that does this. Unfortunately there's no really easy way to do this. You can write a recursive procedure, using a cursor; or you can write a non-recursive one that uses a table variable (or temporary table). For an example of the latter, go to Books Online and look for "hierarchical information" in the Index.
August 11, 2003 at 11:06 am
Yep, my mistake. Looks liek ON CASCADE DELETE is the only elegant option. My apologies.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply