Run stored procedure on query notification without SqlDependency

Question

Greeting everyone!

In SqlDependency you can easy subscribe to data change using Query Notification mechanism. (or by setting odbc attributes)

 SqlDependency dependency = new SqlDependency(
      new SqlCommand("SELECT [ID], [Name] FROM [dbo].[tbl_Contact]", this.CurrentConnection)
 );
 dependency.OnChange += this.dependency_OnChange;

On the other hand, using native sql you can execute stored procedure on some DMV event. (like user logout)

create queue [myEventQueue] with activation (
   status = on,
   procedure_name = dbo.QueueProcessing,
   max_queue_readers = 2,
   execute as self
)

create service [myNotifications] on queue [myEventQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

CREATE EVENT NOTIFICATION [myEvent]
ON server
FOR AUDIT_LOGOUT
TO SERVICE 'myNotifications', 'current database'

My question is:

1. Can we create and subcribe some event query to data change without SqlDependency (using native t-sql in Managment Studio)?
2. Can we execute stored procedure when "some data modified"?

Thank you for any help!

P.S. Why i can't use triggers?

I have about 200 "events" wich are dependent on multiple tables with different predicates (filters). Unfortunately, users can change it.

Answer 1

Instead of using SqlDependency you can use the SqlNotificationRequest class.

From MSDN article Enabling Query Notifications:

...SqlNotificationRequest requires you to implement the entire listening infrastructure yourself. In addition, all the supporting Service Broker objects such as the queue, service, and message types supported by the queue must be defined. This manual approach is useful if your application requires special notification messages or notification behaviors, or if your application is part of a larger Service Broker application.

But this still does not let you subscribe to a data change notification with native T-SQL code. I suppose it could be possible to create a CLR function to submit a notification subscription though.

Also, MS SQL Server has "Change Tracking" features that maybe be of use to you. You enable the database for change tracking and configure which tables you wish to track. SQL Server then creates change records on every update, insert, delete on a table and then lets you query for changes to records that have been made since the last time you checked. This is very useful for syncing changes and is more efficient than using triggers. It's also easier to manage than making your own tracking tables. This has been a feature since SQL Server 2005.

How to: Use SQL Server Change Tracking

Change tracking only captures the primary keys of the tables and let's you query which fields might have been modified. Then you can query the tables join on those keys to get the current data. If you want it to capture the data also you can use Change Capture, but it requires more overhead and at least SQL Server 2008 enterprise edition.

Change Data Capture

Using these features, you would still have to create some service or SQL Agent job that periodically looks at the change tables and sends off the appropriate Service Broker messages to your services.