October 9, 2013 at 12:51 pm
I am building a CDC on my tables. However, I noticed, if I add a new column to the table being audited, the Audit table doesnot have this column. Instead, the only way I can see is to disable the CDC on the table and re-enable it, which menas the data sitting in the audit table is Lost.
Is there any way this can be done seamlessly in CDC?
--EXEC sys.sp_cdc_enable_db
--EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'AuditTest', @role_name = NULL
/*
Operation 1 = Delete
Operation 2 = Insert
Operation 3 = Old value before the operation
Operation 3 = New Value after the Operation
*/
CREATE TABLE dbo.AuditTest(RowID INT IDENTITY(1,1),VALUE VARCHAR(50),UserName VARCHAR(50), CreationDate DATETIME DEFAULT GETDATE(),ModifiedDate DATETIME)
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', -- sysname
@source_name = 'AuditTest', -- sysname
@capture_instance = NULL, -- sysname
@role_name = NULL -- sysname
INSERT INTO dbo.AuditTest
( VALUE ,UserName )
VALUES ( 'QQQ' ,'User123' )
-------THIS NEW COLUMN ADDED IS NOT AVAILABLE IN "dbo_AuditTest_CT"--------
ALTER TABLE AuditTest add TESTCol varchar(10)
UPDATE AuditTest SET Value='TESTINGzzz' WHERE Value='QQQ'
DELETE from AuditTest
select CASE
WHEN [__$operation]=1 THEN 'DELETE'
WHEN [__$operation]=2 THEN 'INSERT'
WHEN [__$operation]=3 THEN 'OLD VALUE'
WHEN [__$operation]=4 THEN 'NEW VALUE'
END AS OPERATION
,*
from cdc.dbo_AuditTest_CT
Also, what are the other ways we can Audit transactional changes to the tables?
Cheers
Ram
October 11, 2013 at 6:31 am
bump
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply