SQL Server tables auditing approach

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Marius Tarnat
    New Member
    • Nov 2013
    • 1
    #1

    SQL Server tables auditing approach

    I am dealing with 2 tables that have identical structure. First one is the main table, and I use the second one for auditing via trigger – it just holds the current condition before auditing.

    Is there a better practice to audit data changes and what is it?

    My data is delicate, but on the other side – I am not planning to update it too many times.
    Tags: None
    • David Townsend
      New Member
      • Nov 2013
      • 4
      #2
      Maybe a two table design can work for you. I suggest having one table to keep the transaction data (database, schema, date, app that triggered transaction, etc.) and another table to hold the record modifications, so you are able to undo them easily.

      There is also a SQL Server Change Data Capture (CDC) feature you could use, as well as some commercial tools meant to deal with this.

        Comment

        • ck9663
          Recognized Expert Specialist
          • Jun 2007
          • 2878
          #3
          Don't forget to use transaction in your trigger so any unsuccessful update of your main table will be rolled back.

          Good Luck!!!


          ~~ CK

            Comment

            Previous template Next
            Working...