Post reply

sys.sp_cdc_enable_table does not consider additions or removal of columsn to the table

  • October 9, 2013 at 12:51 pm

    #284428

    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

    #1657881

    bump

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply