Saya membuat pengaturan untuk memelihara tabel dengan data historis menggunakan CDC. Di bawah ini adalah skrip yang saya tulis:
create table dbo.Name (ID int not null primary key clustered identity(1,1),
Col1 nvarchar(50) not null constraint DF_Col1 default 'Unknown',
Col2 nvarchar(50) not null constraint DF_Col2 default 'Unknown',
Col3 nvarchar(50) not null constraint DF_Col3 default 'Unknown',
Col4 nvarchar(50) not null constraint DF_Col4 default 'Unknown',
Col5 nvarchar(50) not null constraint DF_Col5 default 'Unknown',
CreatedDate DATETIME NOT NULL DEFAULT(GETDATE()),
ModifiedDate DATETIME
)
GO
exec sys.sp_cdc_enable_db
go
exec sys.sp_cdc_enable_table @source_schema = N'dbo',
@source_name = N'Name',
@capture_instance = 'Name',
@supports_net_changes = 1,
@role_name = NULL
GO
INSERT INTO dbo.Name
VALUES('A','B','C','D','E',GETDATE(),NULL),
('F','G','H','I','J',GETDATE(),NULL),
('K','L','M','N','O',GETDATE(),NULL)
GO
SELECT * FROM cdc.Name_CT
UPDATE Name
SET Col1 = Col1 + '_U', ModifiedDate = GETDATE()
WHERE id = 1
UPDATE Name
SET Col2 = Col2 + '_V', ModifiedDate = GETDATE()
WHERE id = 2
UPDATE Name
SET Col3 = Col3 + '_A', Col4 = Col4 + '_B', ModifiedDate = GETDATE()
WHERE id = 3
GO
SELECT * FROM cdc.Name_CT
Skrip di atas akan mengembalikan nilai kolom yang datanya telah diubah. Saya mencari output di bawah ini yaitu kolom FieldName akan berisi daftar kolom tempat data diperbarui dan kolom nilai akan berisi nilai sebelumnya dan nilai baru. Nilai CreatedDate akan menjadi StartDate dari baris sebelum update dan ModifiedDate akan menjadi EndDate dari baris sebelum update dan StartDate dari baris setelah update.
ID FieldName Value StartDate EndDate
=================================================================
1 Col1 A 2014-08-18 15:56:08 2014-08-18 15:59:44
1 Col1 A_U 2014-08-18 15:59:44 NULL
2 Col2 G 2014-08-18 15:56:08 2014-08-18 15:59:44
2 Col2 G_V 2014-08-18 15:59:44 NULL
3 Col3 M 2014-08-18 15:56:08 2014-08-18 15:59:44
3 Col3 M_A 2014-08-18 15:59:44 NULL
3 Col4 N 2014-08-18 15:56:08 2014-08-18 15:59:44
3 Col4 N_B 2014-08-18 15:59:44 NULL