SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Trigger Based Change Data capture

By Ankush Parab,


SQL Server has in-built methods to capture changes occurring to tables. Eg. Change data Capture, Change tracking and auditing. However, they have some shortcomings. Such as, CDC does not provide "user" who made the change or Change tracking does not provide before and after image of data, etc.

This trigger based solution provides simple way to capture changes occuring to table and its designed t ocapture changes from all tables to single log table. The change data is logged in JSON format.

Sample json payload for reference:-

Snapshot of Changelog table:-

How to use:

The script can be used to create triggers on mulitple tables by providing comma seperated list which need to be setup in "configuration" section of the script along with schema name. The script has to be run against intended database.

Total article views: 316 | Views in the last 30 days: 3
Related Articles

Change Data Capture - SQL Server 2008

Change Data Capture (CDC) records DML operations performed on SQL tables and makes records available...


SQL Server 2008 Change Data Capture

SQL Server 2008 has a new Change Data Capture feature that allows you to track Inserts, Updates, and...


Back to Basics: Capturing Baselines on Production SQL Servers

If you have not been capturing baselines on your production servers, then today is the day you can s...


Streaming ETL: SQL Change Data Capture (CDC) to Azure Event Hub

[read this post on Mr. Fox SQL blog] I had a recent requirement to capture and streamreal-time data...


Webinar Q&A: Getting Started with Change Data Capture

Yesterday I presented about Change Data Capture for the Pragmatic Works Free Training on the T’s. I ...