如何在SQL Server 2008 R2中捕获表级数据变化？
我有大量数据标准化为 100 多个表。有多个应用程序会更改这些表中的基础数据，我想针对这些更改引发事件。我知道的可能的选项是：
- 更改数据捕获
- 更改跟踪
- 在每个表上使用触发器（不好的选择，但可能）
如果有人以前已经这样做过，有人可以分享执行此操作的最佳方法吗？
最后我真正想要的是，如果有一个事务影响了 100 个表中的 12 个，我应该能够冒泡一个事件而不是 12 个。假设有并发用户更改这些表。
I have high volume of data normalized into more than 100 tables. There are multiple applications which change underlying data in those tables and I want to raise events on those changes. Possible options that I know of are:
- Change Data Capture
- Change Tracking
- Using Triggers on each table (bad option but possible)
Can someone share the best way of doing this if someone has already done this before?
What I really want in the end is if there is one transaction that affected 12 tables off 100 I should be able to bubble one event up instead of 12. Assume there are concurrent users change these tables.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我能想到的两个选项：
Two options I can think of:
一种可能是 SQL Server 查询通知：使用查询通知
One possibility is SQL Server Query Notifications: Using Query Notifications
只要您想“批量”进行多个更改，我认为您应该遵循更改数据捕获或更改跟踪的路线（取决于您是否只想知道发生了什么变化或发生了什么变化）。
它们应该由“轮询”过程使用，您每隔几分钟（秒，毫秒？？？）轮询一次更改并引发事件。这样做的好处是，只要您存储每个表的前一次轮询的最后一个行版本，您就可以随时检查自上次轮询以来的更改。您不依赖于实时触发方法，如果停止，您将永远丢失所有事件。该过程可以在检查每个表的过程中轻松创建，并且您只需要另外 1 个表来存储每个表的最后一个行版本。
此外，这种方法的开销将由您以及轮询发生的频率来控制。
As long as you want to 'batch' multiple changes, I think you should follow the route of Change Data Capture or Change Tracking (depending on whether you just want to know that something changed or what changes happened).
They should be used by a 'polling' procedure, where you poll for changes every few minutes (seconds, miliseconds???) and raise events. The nice thing about this is that as long as you store the last rowversion of the previous poll -for each table- you can check whenever you like for changes since the last poll. You don't rely on a real time triggers approach, that if halted you would loose all events forever. The procedure could be easily created inside a procedure that checks each table and you would need only 1 more table to store last rowversion per table.
Also, the overhead of this approach would be controlled by you and by how frequently the polling happens.