Change tracking refers to documenting all insert, update, and delete activities that are applied to tables of the database. These changes can then be viewed to find out who accessed the data and when they accessed it. There are two ways to do it:
- Using triggers
- Using change data capture (CDC)
You can use triggers to create an audit trail of activities in one or more tables of the database. The section “AFTER Triggers” in Chapter 14 and Example 14.1 show how triggers can be used to track such changes. Therefore, the focus of this section is CDC.
CDC is a tracking mechanism that you can use to see changes as they happen. The primary goal of CDC is to audit who changed what data and when, but it can also be used to support concurrency updates. (If an application wants to modify a row, CDC can check the change tracking information to make sure that the row hasn’t been changed since the last time the application modified the row. This check is called a concurrency update.)
NOTE CDC is available only in the Enterprise and Developer editions.
Before a capture instance can be created for individual tables, the database that contains the tables must be enabled for CDC, which you do with the system stored procedure sys.sp_cdc_ enable_db, as shown in Example 12.27. (Only members of the sysadmin fixed server role can execute this procedure.)
Example 12.27
USE sample;
EXECUTE sys.sp_cdc_enable_db
To determine whether the sample database is enabled for CDC, you can retrieve the value of the column is_cdc_enabled in the sys.databases catalog view. The value 1 indicates the activation of CDC for the particular database.
When a database is enabled for CDC, the cdc schema, cdc user, metadata tables, and other system objects are created for the database. The cdc schema contains the CDC metadata tables as well as the individual tracking tables that serve as a repository for CDC.
Once a database has been enabled for CDC, you can create a target table that will capture changes for a particular source table. You enable the table by using the system stored procedure sys.sp_cdc_enable_table. Example 12.28 shows the use of this stored procedure.
NOTE The SQLServerAgent service must be running before you enable tables for CDC.
Example 12.28
USE sample;
EXECUTE sys.sp_cdc_enable_table
@source_schema = N’dbo’, @source_name = N’works_on’,
@role_name = N’cdc_admin’;
The sys.sp_cdc_enable_table system procedure in Example 12.28 enables CDC for the specified source table in the current database. When a table is enabled for CDC, all DML statements are read from the transaction log and captured in the associated change table. The @source_schema parameter specifies the name of the schema in which the source table belongs. @source_name is the name of the source table on which you enable CDC. The @role_ name parameter specifies the name of the database role used to allow access to data.
Creating a capture instance also creates a tracking table that corresponds to the source table. You can specify up to two capture instances for a source table. Example 12.29 changes the content of the source table (works_on).
Example 12.29
USE sample;
INSERT INTO works_on VALUES (10102, ‘p2’, ‘Analyst’, NULL);
INSERT INTO works_on VALUES (9031, ‘p2’, ‘Analyst’, NULL);
INSERT INTO works_on VALUES (29346,’p3′, ‘Clerk’, NULL);
By default, at least one table-valued function is created to access the data in the associated change table. This function allows you to query all changes that occur within a defined interval. The function name is the concatenation of cdc.fn_cdc_get_all_changes_ and the value assigned to the @capture_instance parameter. In this case, the suffix of the parameter is dbo_works_on, as Example 12.30 shows.
Example 12.30
USE sample;
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_works_on
(sys.fn_cdc_get_min_lsn(‘dbo_works_on’), sys.fn_cdc_get_max_lsn(), ‘all’);
The following output shows part of the result of Example 12.30:
Example 12.30 shows all changes that happened after the execution of the three INSERT statements. If you want to track all changes in a certain time interval, you can use a batch similar to the one shown in Example 12.31.
Example 12.31
USE sample;
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SELECT @from_lsn =
sys.fn_cdc_map_time_to_lsn(‘smallest greater than’, GETDATE() – 1);
SELECT @to_lsn =
sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’, GETDATE());
SELECT * FROM
cdc.fn_cdc_get_all_changes_dbo_works_on (@from_lsn, @to_lsn, ‘all’);
The only difference between Example 12.31 and Example 12.30 is that Example 12.31 uses two parameters (@from_lsn and @to_lsn) to define the beginning and end of the time interval. (The assignment of time boundaries is done using the sys.fn_cdc_map_time_to_lsn() function.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.