June 9, 2004 at 3:00 pm
I'll try to reduce this to the simplest possible form to make the point easier to understand. I have a table called "Files". The purpose of this table is to "attach" files to a record in a parent table. In the Files table are an ID, a filename, a file extension, and a foreign key called parentID. So it looks like this...
fID Filename Extension ParentID
1 document doc 12
2 spreadsheet xls 13
3 textfile txt 12
4 document doc 19
Each of these records represents a file in a single directory on the network. The path to that network directory is available from another table in the database in a single record (a "SysPrefs" table where I tell the system the path to the attached files). The names of those files are 1.tcf, 2.tcf, 3.tcf, and 4.tcf (tcf is the generic file extension I'll be using). I want to do it this way so that duplicate filenames aren't a problem, and so that the nature of the files remains ambiguous to snooping users. Here's the question...
Let's say I delete parent record 13, so I also issue the statement:
DELETE FROM Files WHERE ParentID = 13
Is there an easy way that the database can - given the information available for each of those records - delete the file on the network automatically whenever the corresponding record in the files table is deleted? For the example above, therefore, I would want to delete 1.tcf and 3.tcf. I was thinking of using a trigger - but I'm not sure how to issue a command from SQLServer that can delete a file at a network location. Is this even possible?
June 11, 2004 at 7:47 am
Given that you know the entire path of the file, you should be able to add (to your procedure) an xp_cmdshell call to delete the file.
However, security is of the utmost concern. So make sure that your user only has access to the appropriate directory or directories involved here.
Depending on the file, you may want to store the 'whole' file in the database. Also an option depending on size.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply