sql server data warehouse: Newbie, Howto create trigger to delete an external image file -- sql server data warehouse

RE: Newbie, Howto create trigger to delete an external image file

Rodney Mullins
5/19/2005 4:33:22 PM

sql server data warehouse:

Now, I have to make some assumptions about your configuration such as:
the ImageFileName also contains the full path and file name, also you have
appropriate permissions for the deletion to take place.

Here are a couple of options:

1. The trigger calls a stored procedure which based on parameters passed
with delete the file using xp_cmdshell 'del imagefilepathandname'

2. The trigger calls a stored procedure which based on parameters passed
with delete the file using FileSystemObject:

DECLARE @hr int
DECLARE @FS int

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
@FS OUT

EXEC @hr = sp_OAMethod @FS, 'DeleteFile',
NULL, ImageFilePathandName

EXEC @hr = sp_OADestroy @FS

There are many other options, but here are two to help get you started. You
definitely want to think about impact on performance.

[quoted text, click to view]

Newbie, Howto create trigger to delete an external image file

robdob2003 NO[at]SPAM yahoo.com
5/19/2005 6:51:05 PM

Hello,

I'm using SQL2000 and I have a table which has a field within it called
"ImageFileName", what I would like to do is create a trigger to
automatcially delete the disk resident image file that is contained within
the "ImageFileName" field of the table upon the deletion of any of these
records.

Is this possible or is this not the preferred method of doing this.

Thank you..

Re: Newbie, Howto create trigger to delete an external image file

Darren Green
5/20/2005 12:19:36 AM

In message <#N918UMXFHA.1152@tk2msftngp13.phx.gbl>, robdob2003@yahoo.com
writes
[quoted text, click to view]

I don't think this would be a good idea. One way I can think of would be
to use xp_cmdshell, but the security implications of this are not good.
By default anybody that induced the trigger to fire would have to be a
sysadmin. Another method would be to use the OLE stored procs, but again
a security risk.

Many environments would not allow this either, because application
servers hold files, SQL is obviously SQL and the two normally have a
firewall in between, and file access is a big no no.

Perhaps try and tackle if from outside of SQL, and maybe use the trigger
to create a work table for an external process to query, so it knows
what files to delete, and then remove the rows from the work table once
done.

Please try and select an appropriate group, rather than all of them.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server data warehouse group.