CODEWITHSUNDEEP
sql-serverdatabase-schema
komratanomin
komratanomin

Reputation: 85

Schema for tracking SQL Server table updates

I have a set of 16 SQL Server tables, which get updated constantly from a Web UI. I need to track every change happening to these tables and call a separate system every 10 minutes sending each inserted or updated row through a Windows service.

I can duplicate the schema and create another set of 16 similar tables to track changes in the original set. There will be triggers that insert a new row into tracking tables (plus ins/upd flag, timestamp etc fields) every time a corresponding source table is modified.

I am wondering is there any better way I can do this using 1 (or few) common tables that can hold data from multiple tables? Something that does not force me to maintain a duplicate set of tracking tables?

Upvotes: 0

Views: 102

Answers (1)

alroc
alroc

Reputation: 28194

If you have Enterprise Edition, Change Data Capture is an option which is available to you.

Other editions have Change Tracking, which doesn't track history but can get you the net changes.

Comparison: https://technet.microsoft.com/en-us/library/Cc280519(v=SQL.105).aspx

Upvotes: 3

Related Questions