Change Data Capture untuk Audit Trail Table

Change data capture adalah fitur untuk melakukan capturing terhadap perubahan yang terjadi pada sebuah table dalam sebuah database. Dengan CDC ini memungkinkan semua operasi yang dilakukan pada sebuah table bisa kita tracking history-nya dari operasi yang dilakukan, dari mulai data pertama kali dimasukkan, perubahan terhadap data di setiap kolom, hingga data terakhir yang ada pada sebuah table.

Untuk membuat CDC ini, kita bisa lakukan  dengan terlebih dahulu mengaktifkan fitur CDC pada sebuah database dengan perintah berikut ini:

EXEC sys.sp_cdc_enable_db;

GO

Perintah ini akan mengaktifkan fitur CDC di database yang kita akan buatkan Change Data Capture-nya, ini bisa dilihat dari adanya file tambahan pada database kita di bagian system table seperti:

• • cdc.captured_columns
• • cdc.change_tables
• • cdc.ddl_history
• • cdc.index_columns
• • cdc.lsn_time_mapping

Dengan CDC ini memungkinkan kita untuk dapat melakukan pencatatan terhadap perubahan data yang terjadi tanpa harus membuat table auditlog sendiri maupun membuat mekanisme trigger untuk mencatat sejarah perubahan data baik oleh aplikasi maupun oleh DBA secara back end.

Berikut contoh scipt untuk fitur change data capture, silahkan mencoba.

--set current database context USE [AdventureWorks]

GO

--append new user column to the HumanResources.Employee Table
ALTER TABLE humanresources.employee
ADD [User] NVARCHAR(50)

GO

--disable any triggers
DISABLE Trigger ALL ON HumanResources.employee

GO

--enable CDC for AW
EXEC Sp_cdc_enable_db

GO

--enable CDC for HumanResources.Employee
EXEC Sp_cdc_enable_table
humanresources ,
employee ,
NULL ,
1 ,
dbo

GO

--create sample UDF to return change data
CREATE FUNCTION [dbo]. [Udf_employee]
(@start_time DATETIME,
@end_time   DATETIME)
RETURNS @Employee TABLE(employeeid       INT,
nationalidnumber NVARCHAR(15),
contactid        INT,
managerid        INT,
title            NVARCHAR(50),
birthdate        DATETIME,
maritalstatus    NCHAR(1),
gender           NCHAR(1),
hiredate         DATETIME,
salariedflag      [FLAG] ,
vacationhours    SMALLINT,
sickleavehours   SMALLINT,
currentflag       [FLAG] ,
rowguid          UNIQUEIDENTIFIER,
[user]           NVARCHAR(50),
cdc_operation    VARCHAR(1))
AS
BEGIN --declare local variables to hold LSNs
DECLARE  @from_lsn BINARY(10),
@to_lsn   BINARY(10)

--Map the time interval to a change data capture query range.
IF (@start_time IS NULL)
BEGIN
SELECT @from_lsn = sys.Fn_cdc_get_min_lsn('HumanResources_Employee')
END
ELSE
BEGIN
SELECT @from_lsn = sys.Fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)
END

IF (@end_time IS NULL)
BEGIN
SELECT @to_lsn = sys.Fn_cdc_get_max_lsn()
END
ELSE
BEGIN
SELECT @to_lsn = sys.Fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)
END

--if same then exit
IF (@from_lsn = sys.Fn_cdc_increment_lsn(@to_lsn))
BEGIN
RETURN
END

-- Query for change data
INSERT INTO @Employee
SELECT employeeid,
nationalidnumber,
contactid,
managerid,
title,
birthdate,
maritalstatus,
gender,
hiredate,
salariedflag,
vacationhours,
sickleavehours,
currentflag,
rowguid,
[user],
CASE __$operation
WHEN 1
THEN 'D'
WHEN 2
THEN 'I'
WHEN 4
THEN 'U'
ELSE NULL
END AS cdc_operation
FROM   cdc.Fn_cdc_get_net_changes_humanresources_employee(@from_lsn,@to_lsn,'all')

RETURN
END

GO

--sample DML statement
UPDATE humanresources.employee
SET    title = 'Marketing Director',
[User] = system_user
WHERE  employeeid = 13

--get the net change of the sample DML including the User
SELECT *
FROM   [dbo]. [Udf_employee] (NULL,NULL)