How to capture table level data changes in SQL Server 2008 R2?

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 ar开发者_JS百科e concurrent users change these tables.

Two options I can think of:

1. Triggers ARE the right way to capture change events in the DB layer
2. Codewise, I make sure in my app that each table is changed through only one place in the code, regardless what the change is (I call it a hub for that table, as it channels many different pathways into one place), it becomes very easy to catch change events that way in the code layer

One possibility is SQL Server Query Notifications: Using Query Notifications

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.