"Ryan" <rv*******@aol. com> wrote in message
news:3d******** *************** ***@posting.goo gle.com...
I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):
xp_cmdshell "delete <filename>"
If some one could please help I would appreciate it very much. I
would love a code sample if you have one. Thank you so very much.
From,
Ryan
You can use xp_cmdshell in a trigger, but this may not be the best approach,
especially if there are very frequent deletes. Calling xp_cmdshell is a
relatively slow operation, and everything will be blocked while the trigger
runs. Also, if there are any problems executing xp_cmdshell, such as a
timeout on a network drive, or a slow file delete, then there may be a
significant impact on your main table. Finally, if someone deletes 20 rows
in a single delete statement, you will have to iterate through them with a
loop or cursor in your trigger, calling xp_cmdshell for each file name, and
that could really cause performance problems.
A more flexible approach might be to use the trigger to put the file name(s)
into a 'pending deletion' table, then have a SQL Server job which checks the
table every few minutes and deletes the file. Using xp_cmdshell for that
would be fine, because you don't care (as much) if it times out. Or even
have an external script read the table and do the deletion from the
operating system side. By doing it that way, any unexpected behaviour won't
impact your main processing.
Simon
