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 ,
,
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)

Comments (0)

Skip to main content