Best Solution to Create a change History for several SQL Tables

Question

I've searched a long time and don't found an solution here yet. Also this question is a little bit subjectiv. But i really need a good solution for this task.

I have a table with Volumes, Security and much more informations. This data are daily changed. Now i need to keep this changes in a history.

What is the best Solution to do this? Making a Second table with the same Structure and just insert the "old" data into this table? Or just making an additional parameter with Status?

Making this server side with triggers? Or programmatically with a LINQ procedure?

Answer 1

We chose separate tables as it will keep your primary table lean and faster. We also opted for triggers, reasoning that if you ever change the entry mechanism, you wouldn't want to have to rewrite your auditing. Also it can capture accidental DBA side changes.

Since update is effectively a delete and then an insert, you can achieve what has been suggested with a single trigger- this is what we did.

• Create a table exactly matching your existing table, but with some added columns: AUDIT_GUID VARCHAR(40), AUDIT_TIMESTAMP DATETIME, AUDIT_ACTION VARCHAR(20)

• Create an "AFTER INSERT,DELETE,UPDATE" Trigger using the following general pattern (simply add more columns where necessary).

  CREATE TRIGGER CustomerAudit ON Customer 
  AFTER INSERT,DELETE,UPDATE AS 
  BEGIN 
      IF (TRIGGER_NESTLEVEL()>1) RETURN
  
      DECLARE @Time DateTime = CURRENT_TIMESTAMP
      DECLARE @Audit_GUID varchar(100) = NEWID()  
  
      INSERT INTO Customer_History (FirstName, LastName, Audit_Date, Audit_Action, Audit_GUID)
      SELECT 
          FirstName, LastName, @Time, 'Delete', @Audit_GUID
      FROM 
          Deleted
  
      INSERT INTO Customer_History 
  (FirstName, LastName, Audit_Date, Audit_Action, Audit_GUID) 
      SELECT 
          FirstName, LastName, @Time, 'Insert', @Audit_GUID 
      FROM 
          Inserted 
  END
  

If you want to find updates, they will be rows in the history table that have a delete & update with the same Audit_GUID value. The timestamp also allows you to check changes made at a certain time, and we've also added a currentuser to find the person to blame if necessary!

Answer 2

I would opt for a ON DELETE/ON UPDATE trigger that stores deleted or modified rows to a second table.

This way you

• can keep up referential integrity
• can build sensible unique keys in your "live data" table
• don't have to remember adding WHERE IsDeleted = 'N' (or the like) with every query you make
• have automatic historization with your data even if you modify it directly on the database

This keeps usability and performance up with your live data. For historic data you'd have to go to your history tables, which in my experience can be cumbersome if the queries are complex and you want to mix live and historic data into one result. Depends on your use case if that kind of usage is a likely scenario.

Answer 3

I think its better to create a separate table to track the history along with required fields and date, so that you can query this table as and when required. You can use triggers to log.