MS SQL Data Warehousing - Newbie, Howto create trigger to delete an external image file

Author

Newbie, Howto create trigger to delete an external image file

2005-05-19, 8:23 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..

Darren Green

2005-05-19, 8:23 pm

In message <#N918UMXFHA.1152@tk2msftngp13.phx.gbl>, robdob2003@yahoo.com
writes
>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..
>

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

Rodney Mullins

2005-05-19, 8:23 pm

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.

"robdob2003@yahoo.com" wrote:

> 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..
>
>
>

Sponsored Links