在没有SqlDependency的情况下在查询通知上运行存储过程

Greeting everyone!

问候大家！

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

在SqlDependency中，您可以使用Query Notification机制轻松订阅数据更改。（或通过设置odbc属性）

 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)

另一方面，使用本机sql可以在某些DMV事件上执行存储过程。（比如用户注销）

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:

我的问题是：

Can we create and subcribe some event query to data change without SqlDependency (using native t-sql in Managment Studio)?
我们可以在没有SqlDependency的情况下创建和描述一些事件查询到数据更改（在Managment Studio中使用本机t-sql）吗？
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.

我有大约200个“事件”，它们依赖于具有不同谓词（过滤器）的多个表。不幸的是，用户可以改变它。

1 个解决方案

#1

Instead of using SqlDependency you can use the SqlNotificationRequest class.

您可以使用SqlNotificationRequest类，而不是使用SqlDependency。

From MSDN article Enabling Query Notifications:

从MSDN文章启用查询通知：

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

... SqlNotificationRequest要求您自己实现整个监听基础架构。此外，必须定义所有支持的Service Broker对象，例如队列支持的队列，服务和消息类型。如果您的应用程序需要特殊通知消息或通知行为，或者您的应用程序是更大的Service Broker应用程序的一部分，则此手动方法非常有用。

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.

但是，这仍然不允许您使用本机T-SQL代码订阅数据更改通知。我想可以创建一个CLR函数来提交通知订阅。

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.

此外，MS SQL Server具有“更改跟踪”功能，可能对您有用。您启用数据库以进行更改跟踪并配置要跟踪的表。然后，SQL Server会在每次更新，插入，删除表上创建更改记录，然后让您查询自上次检查以来所做记录的更改。这对于同步更改非常有用，并且比使用触发器更有效。它比管理自己的跟踪表更容易管理。这是自SQL Server 2005以来的一项功能。

How to: Use SQL Server Change Tracking

如何：使用SQL Server更改跟踪

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 Capture，但它需要更多的开销，至少需要SQL Server 2008企业版。

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.

使用这些功能，您仍然需要创建一些服务或SQL代理作业，定期查看更改表并将相应的Service Broker消息发送到您的服务。

#1