xml Chang Log and Triggers - Is this horrible or good? 1

[iwease]

I'm doing a project where we want to store the changes users make to a database so that they can be reverted. At first, this seemed like a good idea but the more and more I think about it, the more I realize that each update/delete/insert is going to have a lot of overhead.

So here is our design. We will store all the changes in a single table that will have date, tableID, userID, and then a text field that will contain a bunch of xml for each field that has changed (maybe the old values and the new values). So, I think this is ok.

The problem is populating the change log table. I was thinking of either

(1)creating a stored procedure for it and forcing users to call the procedure themselves before making an update
(2)creating a trigger that will automatically put the changes in the appropriate xml formate and then populate the DB

I'm pretty sure I'm going to go with method (2) but am just wonding if this is a horrible idea or a good idea

 

[SQLSister]

A trigger is the way to go, otherwise you will not get reliable data. I guarantee the one guy who didn't run the stored proc is the one you will need to recover. One thing to make sure you test is scripts to get the data back out of the audit table. When you need to do this, the need will be urgent and it's no time to be figuring out how to get the data back. That may end up affecting your structure of how you store the data, so look at it early on.

You may also need a job to clean out the audit table of any data over a certain age (like a week or 6 months or whatever you feel you need to keep). This is critical or these tables become huge.

Questions about posting. See faq183-874

 

[iwease]

Is it a problem if the table becomes huge? I mean, if it is storing millions of records, it still shouldn't be a problem since I expect that users wil rarely be retrieving/reverting data

 

[SQLSister]

By huge I mean can take up your entire hard drive. These tables can end up much larger than the base tables. I won't suggest how far back you need to keep, this is based on regulary requirements some places. Other wise, it is rare to need to recover more than a week's worth from an audit log, but you may need to look up who made a particular change several months back.

Questions about posting. See faq183-874

 

[Crowley16]

would it not be easier just to save the backups and transaction logs?

not quite as easily searchable as a table, but if the unthinkable does occur, then you can just restore upto whatever point you need and get the data off...

--------------------
Procrastinate Now!

 

[iwease]

yeah, that would be a good idea.

Now, my only problem with the trigger system is that I have to implement it for about 30 tables...this means 30 triggers. The code for all the triggers is very similar (if not identical). It would be great if I could call a stored procedure from the trigger to run the code. The only problem is the Inserted and Deleted table is not accessible within the stored procedure.

Any ideas? I think I'm just going to have to accept the fact the the code will be repeated 30 times

 

[SQLSister]

Crowley16, suppose you have one record that is messed up and the database is 20 gigs large. It would take hours and lots of extra space to restore a backup of that size and then find the one record you need and you don;t even know when it was changed.

It would take a couple of minutes at best to fix from the audit log. Plus it tell syou who made the change which can be important in determining action (re-training, changing security rights, etc.) that need to happen to avoid in the future.

Questions about posting. See faq183-874