Using table auditing in order to have "snapshots" of table

Question

I need a solution for the following problem:

I will have a table in SQL server 2008 that will be changing daily. It's a small table with maybe 5000 rows at most and around 5 fields.

The client will need the ability to view all the data in the table the way it was at a given point in time. For example "show me what all the data looked like in the table 2 weeks ago".

Someone had mentioned that auditing the table with http://autoaudit.codeplex.com/ would solve this problem.

My questions:

1. Is there a solution to this problem that would involve simple auditing?
2. If so, then how would i use auditing to solve this problem?
3. Is there a different solution already in existence for this sort of challenge so that I do not have to reinvent the wheel?

Answer 1

Have an auditing table that get's manipulated by triggers. Something like:

create table YourAuditTable
(
    -- all of your source table's columns
    CreateDate datetime not null,
    DeleteDate datetime null
)
go

And your trigger would look like this:

create trigger AuditYourTable
on dbo.YourTable
after insert, update, delete
as

    if exists(select * from inserted)
    begin
        if exists(select * from deleted)
        begin
            -- this is for an update
            update YourAuditTable
            set DeleteDate = getdate()
            where YourIDCol in (select YourIDCol from deleted)
        end 

        -- this is just for an insert
        insert into YourAuditTable
        select *, getdate() as CreateDate
        from inserted
    end
    else
    begin
        -- this is just for a delete
        update YourAuditTable
        set DeleteDate = getdate()
        where YourIDCol in (select YourIDCol from deleted)
    end

go

What this will allow you to do is query your audit table for a point in time. In other words, just use DATEDIFF to determine if a row was created prior to that given point in time, and deleted after it (or not deleted at all).

EDIT

To query your audit table for point in time data:

select *
from YourAuditTable
where CreateDate <= @PointInTimeDateTime
and
(
    DeleteDate is null or
    DeleteDate > @PointInTimeDateTime
)

Answer 2

You can also store history directly in your table. You add an activedate and an inactivedate column. If you don't care about the time of the insert or change, then use a date column instead of datetime.

If this is done on an existing sytem, tou change the name of the table. You write a view that shows only those records currently active and name it what the old table used to be called (so all the old code doesn't break).

If this is a new system, create the table with the columns mentioned above and still write a view that gets only the active records. This will enable your developers to consistently use the active view so they don't forget to filter the records when they want to show the data as it stands right now.

You will have to change how you would normally do your update record process (I'd do this in an instead of trigger) so that when a record is updated, it puts in the inactive date in the current record and adds a new one. Do the same with a delete using an instead of trigger to inactivate the record rather than deleting it. If this is anew process, you could skip teh triggers and simply write updates that do the same thing and updates to the inactive date filed when you want to delte. However, I find the triggers are morerelaible in terms of ensuring data integrity. I would personally do the instead of triggers and tehn let developers write normal updates and deletes in the user interface. This would ensure that all chagnes are properly handled whether they came from the GUI or from an adhoc update in SSMS.

Then you write a stored proc with a dateparameter to return the data active on the date input (you may need some special code to handle if a date input was earlier that the data you started doing this), then use this in the GUI form for the user to see the data as of a certain date.