Share via

SQL Server 2008 R2 - Data Level Audit Sample (Change Data Capture)

CREATE DATABASE SQL2008CDC
GO

USE SQL2008CDC
GO

EXEC sp_changedbowner 'sa'
GO

EXEC sp_cdc_enable_db
GO

SELECT [name] AS DBName,is_cdc_enabled FROM sys.databases

CREATE TABLE dbo.Employee
(
EmpID int Primary Key NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL
)

EXEC sp_cdc_enable_table 'dbo', 'Employee', @role_name = NULL, @supports_net_changes =1
GO

select [name], is_tracked_by_cdc from sys.tables
GO

INSERT INTO dbo.Employee
VALUES (1, N'John Wills', <N'jw@contoso.com'>)
GO

UPDATE dbo.Employee SET EmpName = N'Don Miles' WHERE EmpID = 1
GO

SELECT * FROM cdc.dbo_Employee_CT
-- 1 : Delete
-- 2 : Insert
-- 3 : Before Update
-- 4 : After Update

-- Net Changes
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_employee');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_employee(@from_lsn, @to_lsn, 'all')

 

REFERENCE: