History log of record changes...

[divjoy]

Hi , Im using sql server 2008R2.

I am looking for a way to setup a database such that if a user changes any of the data in a table, the previous value is stored in a history file, the name of the user and date and time of change.

But I'm conscious this could cause a major overhead for the database continually recording previous values for every piece of record changed.

So I would like to ask, has anyone done something similar and how they got on and is it worth the effort?

What pitfalls to look out for and just general guidance on the best way to do it?

Thanking you....

 

[jmcilhinney]

If you want to do it in the database then you can look at using triggers. Otherwise, you'll save audit data the same way you do any other data. The main issue to consider is volume of data. How much data will an audit record occupy and how many do you expect to end up storing? Is that volume of data acceptable to you? Workload is not really a consideration unless there is a very high level of activity.

 

[Herman]

What you can do for this is use triggers:

CREATE TRIGGER (Transact-SQL)

How this works is you can setup an audit table, with columns for datetime, tablename, columnname, previousvalue, value, and username, and any other field you want to include, and create a trigger that will insert a row in this table every time a row is inserted or updated in a monitored table. This way this is all managed automatically by the server it requires zero management, other than cleaning up the audit table once in a while.

 

[divjoy]

Triggers

Thanks Folks...

Now I need to find out all about triggers are they easy to setup and what else are triggers used for?

kind regards

Div Joy

 

[jmcilhinney]

The only issue with using a trigger in this situation is that, as far as users are concerned, it only knows about who's logged into the database, not the application user. You might have to save the ID of the user who modified a record in the record itself so that the trigger can then get it from there and save it to the audit table.

 

[divjoy]

Hi,

Yes I started storing in all the tables the username of each user who adds a new record in all of my tables.

So I only need to record 'updated' and 'deleted'. From reading online I see there are two virtual tables called inserted and deleted and through using a combination of these to get the update to work but I will still need to create my own audit tables I think.

Also not sure of the difference between After or For triggers

 

[jmcilhinney]

The advantage of using triggers is that it's all in the database and you'll get appropriate auditing no matter where changes are made form. If the only place that changes should be made from is an application then you also have the choice of managing your auditing there. In that case, you have to add or update a related audit record every time you add or update any other record. If you're auditing then you should never delete anything. If you've written your data access code well, managing auditing in your application is quite simple and really only needs to be done in one place.

 

[jmcilhinney]

Also not sure of the difference between After or For triggers

difference between a FOR and AFTER trigger

 

[jmcilhinney]

I will still need to create my own audit tables I think.

Absolutely.