Create Audit Trail from SQL CDC



  • I have a few tables on which I have enabled CDC. As each row in CDC table contains all the columns regardless if they were changed or not, so it becomes difficult to figure out only the changed data. Is there a way to create a single Audit Table with data in these CDC tables so that it shows Table Name, Column Name, Old Value, New Value from all CDC tables together. If this is possible then a better reporting can be done.

    Thanks



  • Let's suppose your table has columns PK, A & B. After an UPDATE the https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server#change-table will have entires like

    __$operation  PK  A  B
               3   1  X  Y
               4   1  X  Z
    

    omitting the other metadata tables for clarity. If this is https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot ed you get

    __$operation  PK  Column  Value
               3   1  A       X
               3   1  B       Y
               4   1  A       X
               4   1  B       Z
    

    The "before" rows (operation 3) can be separated from the "after" (operation 4) by a WHERE. It may be convenient to wrap each in a view or a CTE. Then the "before" can be joined to "after" on PK and Column. When the Value differs you have a row to insert into the audit table.

    select
      'TheTableName' as TableName,
      a.Column,
      a.PK,
      b.Value as BeforeValue,
      a.Value as AfterValue
    from dbo.Before b        -- a view
    inner join dbo.After a   -- a view
      on a.PK = b.PK
      and a.Column = b.Column
    where a.Value != b.Value;
    

    For a generic audit table common to all application tables this format is good enough. You may decide to aggregate changes made together into a single audit row. This is probably best done as https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server these days, though https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server and simple https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql are also options.

    To handle more than one UPDATE to a row you will involve __$start_lsn as well as PK.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2