如何监控应用程序的数据库更新？
我使用 ODBC、C++ 来处理 SQL Server 数据库。我想检测数据库某些表中的修改：另一个应用程序插入或更新行，我必须检测所有这些修改。它不必是立即触发器，使用轮询定期检查数据库表是否有修改是可以接受的。
以下是我认为可以做到这一点的方法，并且需要您的意见这是否是执行此操作的标准/正确方法，或者是否存在更好的方法。
我的想法是这样的：我在 SQL Server 中添加触发器，在进行任何修改时，触发器会将修改/添加的行的标识符插入到特殊表中，我将定期从应用程序中检查该表。假设有 3 个表：客户、产品、服务。我将创建三个附加表：Change_Customers、Change_Products、Change_Services，并将插入相应表的已修改行的标识符。然后我将定期从应用程序中读取这些 Change_* 表并删除已处理的记录。
现在，如果您同意上述解决方案是正确的，我还有另一个问题：对于我希望监视的每个表都有单独的 Change_* 表更好，还是有一个包含以下更改的胖 Changes 表更好？所有桌子。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
查询通知是一项旨在准确执行任务的技术 > 你所描述的。您可以通过众所周知的 SqlDependency 从托管客户端利用查询通知 类，但也有本机 Ole DB 和 ODBC 方式。请参阅使用查询通知，有关
SSPROP_QP_NOTIFICATION_MSGTEXT (OleDB) 和 <代码>SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT (ODBC)。有关查询通知如何工作的说明，请参阅神秘通知。这是唯一适用于任何类型更新的免轮询解决方案。触发器和更改轮询存在严重的可扩展性和性能问题。更改数据捕获和更改跟踪实际上涵盖了不同的主题（同步偶尔连接的设备的数据集，例如同步框架）。
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) andSQL_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).
更改数据捕获(CDC)--http://msdn.microsoft.com/en-us/library/cc645937.aspx
首先，您需要在数据库中启用 CDC
::
使用数据库名称
去
执行sys.sp_cdc_enable_db
GO
然后在表上启用 CDC
:: sys.sp_cdc_enable_table
然后就可以查询变化
Change Data Capture(CDC)--http://msdn.microsoft.com/en-us/library/cc645937.aspx
First you will need to enable CDC in database
::
USE db_name
GO
EXEC sys.sp_cdc_enable_db
GO
Enable CDC on table then
:: sys.sp_cdc_enable_table
Then you can query changes