CODEWITHSUNDEEP
sqldatabasesql-server-2008file
Ashok Gupta
Ashok Gupta

Reputation: 2297

Can we delete the physical file from server when I delete the corresponding entry from database?

Can we delete the physical file from server when I delete the corresponding entry from database?

i.e., C:\Test\Test.txt -> when deleting this record from database, i need to delete the corresponding Test.txt file from mentioned location. Is there a way, I m using SQL 2008.

Any help would be highly appreciable..

Thanks

Upvotes: 4

Views: 15682

Answers (3)

CloudyMarble
CloudyMarble

Reputation: 37566

I didnt tested this but i think it should work.

Try writing a stored procedure which has the filename as a parameter and delete it using the:

exec master.dbo.xp_cmdshell 'del <Filename>'

then create a trigger for after delete on the table containing the Filenames which calls the stored procedure and provides the Filename from table deleted, or maby you can run directly the command exec master.dbo.xp_cmdshell 'del ' from the trigger.

The better way would be to save the files as an Object in the Database instead of the file path, and when deleting you just delete the file Object.

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21766

The ways are:

  • use of xp_cmdshell proc (exec master..xp_cmdshell 'del C:\Test\Test.txt')
  • use the .NET CLR unsafe proc (need to write in any .NET language and deploy to sql server. Its a long story)

Both ways are ugly

And once again - it is the worst practice. Server should not delete user files, or any files, is they are not integral part of its database.

Upvotes: 3

Werner Henze
Werner Henze

Reputation: 16726

You could use CREATE TRIGGER FOR DELETE to create a trigger that runs when rows are deleted. The SQL statement that is run upon deletion can walk through the table deleted to get the deleted rows. For each row it can exec xp_cmdshell. xp_cmdshell is disabled by default, so you must enable it first using exec sp_configure.

Upvotes: 1

Related Questions