How to take daily snapshots of a table

Question

I am building a sales database. One of the tables has to be a hierarchy of sales reps and their assigned territories. Ohese reps and their territories change every day, and I need to keep track of what exactly that table looks like every day. I will need to take snapshots of the table daily.

I would like to know what I have to do or how I have to store the data in the table, to be able to know exactly what the data in the table was at a certain point in time.

Is this possible?

Please keep in mind that the table will not be more than one megabyte or so.

Answer 1

I suggest using Paul Nielsen's AutoAudit:

AutoAudit is a SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with:

• Created, CreatedBy, Modified, ModifiedBy, and RowVersion (incrementing INT) columns to table
• Insert event logged to Audit table
• Updates old and new values logged to Audit table
• Delete logs all final values to the Audit table
• view to reconstruct deleted rows
• UDF to reconstruct Row History
• Schema Audit Trigger to track schema changes
• Re-code-gens triggers when Alter Table changes the table

His original blog post: CodeGen to Create Fixed Audit Trail Triggers

Before you implement in production suggest you restore a backup of your database into development and work on that.