Implement data rollback in existing CRUD app

Question

I have an existing CRUD app that I have been tasked with implementing "tomb stoning". I need a way to allow a user to roll a given page of data back to the previous state.

Say I have First Name, Last Name, and Social Security Number on a page in this app. User A updates the Last Name field. Later, User B notices that the New Last Name is different and wants to see Who changed it and roll it back if neccessary.

I am unfamilar with this so if I am missing or misusing some terms, forgive me.

This app has a MS SQL backend and the DAL is mostly SPROCS. Right now there isn't any archiving or tomb stoning to speak of.

I had thought to just make a table for each existing table called tblPerson --> tblPersonTombstone and then the roll back portion would read from that table. Unfortunately, the original DB designers designed it in such a way that a Single page in the App might contain info from 2 or 3 different tables. Thus, I would imagine, I need a more Transaction based approach.

Any direction or pointers will be greatly appreciated. Am I on the right track with my thinking? Maybe I am over complicating it? How have others done it?

I see this post How to implement ‘undo’ operation in .net windows application? and also this one New CodePlex project: a simple Undo/Redo framework but I am concered that neither fit my actual situaiton. I am not looking to let the users click ctl+z. I need to let them roll a whole page back to a previous state. If I am misunderstanding the use of those two examples then please say so.

Thanks for the time.

Answer 1

What you're talking about falls under the topic of auditing. Unfortunately, this is one of the more involved implementations.

Here's a Best Practice:

Create new "revision tables" that mirror the tables being audited, but also include some extra metadata (revision #, timestamp, user who made the change, type of CRUD operation).

The idea is to be able to easily get a complete snapshot of the record at any point in time, and then use that to do a perfect rollback. You have complete confidence that the data is right and it just works.

Most folks use triggers to populate those revision records.

There are other solutions as well. Obviously doing it this way is going to be time consuming and disk space-intensive (but hey, you can safely purge old records without breaking the system). The advantage is that you end up with a ton of flexibility.

So that's the way most people do it.

Here's another way:

I have also implemented a simpler auditing pattern that just keeps track of the name of the table that changed, the field that changed, the old value and the new value, along with the usual metadata.

With that, I wrote a plugin for my ORM tool that pretty much handles the saving of auditing data automatically. Otherwise this would have been very tedious.

You might be tempted to go this route. And it's true, you probably can get rollbacks out of it. But it would be more difficult. For instance, if you wanted to restore to any given date and time, you would have to analyze all those individual field-level change records in order to re-create the complete snapshot. And woe unto you if you ever change the name of a field!

So this method of auditing is fine for generating and displaying an audit trail, but if you want to do rollbacks, it has a lot more moving parts, and a lot more things that can go wrong. Heed my words: stay away from this if you need rollbacks, it's going to be more work than just creating those revision tables!

Links

Here's a link on stackoverflow where they talk about implementing auditing in SQL Server, some people mention Change Data Capture which is new in SQL 2008 Enterprise... It doesn't do rollbacks automatically, but it does store audit data:

Suggestions for implementing audit tables in SQL Server?

Answer 2

2 thoughts:

1. Create an archive table that is not necessarily a duplicate of your persisting table(s) but justrepresents the data on the page with the rollback capability. So if the page contains fields that impact multiple tables, your archive table wil contain columns for each alterable field on the page.

2. If the data on the page is encapsulated in a single DTO or entity object, serialize the object before chnging it and store it in anarchive table. Then if the user wishes to roll back, you can deserialize it and then save the deserialized object.