如何监视应用程序的数据库更新？ [英] How to monitor database updates from application?

问题描述

我使用SQL Server数据库与ODBC，C ++。我想检测数据库的某些表中的修改：另一个应用程序插入或更新行，我必须检测所有这些修改。它不一定是立即触发，也可以使用轮询定期检查数据库表以进行修改。

下面是我认为可以这样做的方法，

我想到的是这样的：我添加了触发器SQL Server，在任何修改，将插入修改/添加行的标识符到特殊表，我将从我的应用程序定期检查。假设有3个表：客户，产品，服务。我将创建三个附加表：Change_Customers，Change_Products，Change_Services，并将插入相应表的已修改行的标识符。然后我将从我的应用程序中定期读取这些Change_ *表并删除处理的记录。

现在如果你同意上面的解决方案是正确的，我有另一个问题：对于我想要监视的每个表，我们都有单独的Change_ *表，或者最好有一个胖表，它将包含所有表中的更改。

解决方案

查询通知是技术旨在完全您所描述的内容。您可以通过熟知的 SqlDependency，利用受管客户端的查询通知类，但也有原生Ole DB和ODBC方式。请参见使用查询通知，有关 SSPROP_QP_NOTIFICATION_MSGTEXT （OleDB）和 SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT （ODBC）。有关查询通知如何工作的说明，请参见神秘通知。 p>

这是唯一的无轮询解决方案，适用于任何类型的更新。对更改的触发器和轮询具有严重的可伸缩性和性能问题。更改数据捕获和更改跟踪实际上涵盖了不同的主题（为偶尔连接的设备同步数据集，例如同步框架）。

I work with SQL Server database with ODBC, C++. I want to detect modifications in some tables of the database: another application inserts or updates rows and I have to detect all these modifications. It does not have to be the immediate trigger, it is acceptable to use polling to periodically check database tables for modifications.

Below is the way I think this can be done, and need your opinions whether this is the standard/right way of doing this, or any better approaches exist.

What I've thought of is this: I add triggers in SQL Server, which, on any modification, will insert the identifiers of modified/added rows into special table, which I will check periodically from my application. Suppose there are 3 tables: Customers, Products, Services. i will make three additional tables: Change_Customers, Change_Products, Change_Services, and will insert the identifiers of modified rows of the respective tables. Then I will read these Change_* tables from my application periodically and delete processed records.

Now if you agree that above solution is right, I have another question: Is it better to have separate Change_* tables for each of my tables I wish to monitor, or is it better to have one fat Changes table which will contain the changes from all tables.

解决方案

Query Notifications is the technology designed to do exactly what you're describing. You can leverage Query Notifications from managed clients via the well known SqlDependency class, but there are native Ole DB and ODBC ways too. See Working with Query Notifications, the paragraphs about SSPROP_QP_NOTIFICATION_MSGTEXT (OleDB) and SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT (ODBC). See The Mysterious Notification for an explanation how Query Notifications work.

This is the only polling-free solution that work with any kind of updates. Triggers and polling for changes has severe scalability and performance issues. Change Data Capture and Change Tracking are really covering a different topic (synchronizing datasets for occasionally connected devices, eg. Sync Framework).

这篇关于如何监视应用程序的数据库更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！