Notification when record deleted from SQL Server table?

[Grant Schenck]

I have a table that stores a file name among other things.

If a user deletes a row from the table I want my application to be notified
so it can delete the corresponding file from the file system.

I'm using ADO.NET with C# and I can't seem to find any examples of how (or
if) this can be handled. I'm thinking I need some kind of SQL Server
trigger but I don't see a way to actually have an event with details of the
deleted row sent to my application.

So, is this possible and if so, what is the recommended technology and
techniques?

Thanks in advance.

 

[Jesse Houwing]

* Grant Schenck wrote, On 20-10-2009 16:30:

I have a table that stores a file name among other things.

If a user deletes a row from the table I want my application to be notified
so it can delete the corresponding file from the file system.

I'm using ADO.NET with C# and I can't seem to find any examples of how (or
if) this can be handled. I'm thinking I need some kind of SQL Server
trigger but I don't see a way to actually have an event with details of the
deleted row sent to my application.

So, is this possible and if so, what is the recommended technology and
techniques?

Thanks in advance.

Have a look at SQL Server Notification Services, or invoke a managed
assembly from the delete trigger.

 

[William Vaughn \(MVP\)]

IIRC Notification Services has been deprecated. The problem is setting up a
"back channel" to the application. I expect writing a custom trigger would
be a start but getting it to communicate back to a specific application
would be a challenge--even for a CLR executable.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Gregory A. Beamer]

I have a table that stores a file name among other things.

If a user deletes a row from the table I want my application to be
notified so it can delete the corresponding file from the file system.

Worry to pipe in late, but I just saw this thread.

Delete trigger, as mentioned, is part one of the equation.

The application notification really depends on what type of application
and whether the notification needs to push.

You can easily set up an email notification without any issue. Notifying
an application, however, gets a bit more involved, especially if the
application is not connected full time to the database server.

I WOULD NOT go with Notification Services. Two reasons. 1) There is not
much support and it looks like it is a dinosaur that will not be
developed going forward. 2) It is difficult as hell to work with,
largely because it is a dinosaur and they are not adding additional
tooling (at least not that I know of). It never gained any ground.

I guess the natural question is: What kind of app is it that you need
notification in?

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************