Query Question, Need Help ASAP!

[manmit.walia]

Hello Fellow Members,

Background:

Hopefully someone out there can help me. Here is the situation. I have
a documents table which stores "documents". A document can either be a
folder or a actually document(word, excel ...). This is determined by
a field in the table called 'Type' which is either a '0' or '1'

The table stores the following data for each 'document'
--------------------------------------------------------------------------------
DocID - int
Document - Actual document in bytes
LeafName - Actual document name
Type - Determines type of document (0 - document, 1- folder)
DirName - Which folder the document resides in (can be null)

Problem:

I want to create a query which navigates through the entire table and
deletes all documents. For example, if a user has a folder called
(Temp) and that folder has 30 documents and another folder called
(Temp2) that also has documents. Now the user wants to delete (Temp).
So what I want the query to do is go through the database and delete
then entire folder (Temp) and all of its contents associated with it.

Question:

How can I traverse through the table to delete the documents?

All of your help would be greatfull.

VLead Consulting, Inc.




http://www.vleadconsulting.com

 

[Guest]

Hi,

I do not believe that could be done in one query - the number of levels
below your Temp folder is unknown. You will need to open a cursor (or
equivalent - depending on the database) and walk down your hierarchy tree,
removing content of every subfolder level separately.

 

[Guest]

Although I hate to say it (because I hate them with a passion), using a
database trigger may be the answer. I'm assuming you're using SQL Server.
Create a delete trigger for the table which when you are deleting a folder
will fire to delete all the folders and documents where the DirName is the
name of the folder you are deleting.