Post reply

Dynamic Audit

  • July 21, 2014 at 3:32 am

    #397084

    Hi All,

    I would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,

    I am having 3 tables, table1 table2 table3

    User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).

    I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,

    TableA

    CountryId CountryCode CountryName

    1 IND INDIA

    Update TableA

    Set CountryCode = 'INDI', CountryName = 'INDIA'

    Where CountryId = 1

    On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone. I am giving the sample query for this scenario. Someone help me with this.

    CREATE TABLE [dbo].tCountry(

    CountryId bigint IDENTITY(1,1) NOT NULL Primary Key,

    CountryName [varchar](50) NULL,

    CountryCode [varchar](50) NULL,

    CreateBy bigint

    )

    EXEC sys.sp_cdc_enable_db

    EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N'tCountry',

    @role_name = NULL,

    @captured_column_list = N'CountryId,CountryName,CountryCode', --For these columns CDC will get enabled

    @supports_net_changes = 1

    Select * from cdc.dbo_tCountry_CT

    Insert into tCountry

    Select 'INDIA', 'IN', 25

    --After few seconds, Insered record will show here

    Select * from cdc.dbo_tCountry_CT

    Update tCountry

    Set CountryName = 'INDIA',

    CountryCode = 'IND'

    Where CountryId = 1

    --See here, i want to display CountryName as NULL, because i didnt do any changes in this column

    --Why am i asking this bcoz, Frm my front end application, i will send the complete list of columns and the values to the update query.

    Select * from cdc.dbo_tCountry_CT Where __$operation in (3,4)

  • July 21, 2014 at 4:04 am

    #1730945

    is this as simple as changing

    @captured_column_list = N'CountryId,CountryName,CountryCode'

    to

    @captured_column_list = N'CountryId,CountryCode'

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 21, 2014 at 4:07 am

    #1730947

    Hi,

    Thanks for your reply, but it doesn't make any sense, i am asking solution for a generic way. User may end up with an update of CountryName alone at times and also he may go with updating CountryId alone. So i just wanted to capture only the VALUE WHICH GOT CHANGED bcoz of update statement.

  • July 21, 2014 at 9:19 am

    #1731058

    I'm not sure why people are so hepped-up on wanting to avoid triggers for such a thing. Written correctly, they're very targeted, very fast, require little special knowledge, and require no knowledge of special features (or the related concerns) such as CDC.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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