Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David McKinney » Imaginative Auditing with Rollback (Undo) and...

22 posts, Page 1 of 3

1 2 3 »»»

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I

Rate Topic

Display Mode

Topic Options

Author

Message

David McKinney

Posted Friday, April 17, 2009 2:43 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I

Post #699195

Mike Vassalotti

Posted Monday, May 11, 2009 4:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, January 19, 2013 4:07 AM
Points: 5, Visits: 28

The trigger is very spiffy. Really good!

Mike V

Post #713970

Paul White

Posted Monday, May 11, 2009 5:20 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534

I'm going to wait for the final instalment before really getting into this, but the whole idea seems highly dubious at this stage.

Paul

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #713986

ronmoses

Posted Monday, May 11, 2009 5:38 AM

SSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 649, Visits: 686

Paul White (5/11/2009)

I'm going to wait for the final instalment before really getting into this, but the whole idea seems highly dubious at this stage.

I was thinking the same thing. I look forward to part two explaining how this doesn't create an incredible amount of overhead, in either processing or disc space usage.

ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Post #713995

SuperDBA-207096

Posted Monday, May 11, 2009 5:45 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

Be interesting to see the SQL this generates for a table insert w/ 100 or so columns...

Post #713999

David McKinney

Posted Monday, May 11, 2009 6:27 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

Thanks for your comments.

I agree - it is highly dubious! But hopefully that doesn't make it without merit.

In Part II, I do talk a little (just a little) about why you probably aren't going to want to use this. At least not, "as is".

Firstly I should say that my primary goal in writing this was to see if it could be done! You don't have to think too long to think of reasons why you mightn't want all tables in your database (or database instance) logging to a single column in a single table. (The word 'contention' comes to mind.) Also the trigger is BIG even for a few columns, and there's quite a lot going on.

However, I do think that there are occasions when this could have valid applications. If your data is very slow moving (and some data is) then you might consider it, selectively. I'm also thinking of a specific table or small group of tables where the front end application allows you to perform calculations - and then allows you to keep or discard (undo) the results of these calculations.

Another scenario could simply be when you want to script an insert statement, for a promotion. Using the trigger, you can load the data in the table any way you wish, and then copy from the audit table to script it.

So in some ways it's still a solution waiting for a problem. But at least the day when I discover the precise problem, I'll already have the solution!

Regards,

David McKinney.

Post #714026

Noel McKinney

Posted Monday, May 11, 2009 7:14 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, April 05, 2012 2:35 PM
Points: 2,007, Visits: 767

Thanks David, I'm looking forward to part 2.

Processes involving lookup tables could benefit from a solution like this. Users could undo their changes without remembering the prior value or whether they changed/added/removed a lookup. Some modifications could be needed... for example, adding the user name making the change to the audit table would be helpful.

Post #714078

David McKinney

Posted Monday, May 11, 2009 7:18 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

We McKinney's have got to stick together! (I thought I was the only one in the SQL world!)

Thanks for your support.

(And yes user name is an obvious extension to the audit table. Just a new column with a default value suser_sname.)

Post #714083

Steve Jones - SSC Editor

Posted Monday, May 11, 2009 8:49 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425, Visits: 13,738

Nice job, David.

Everyone look for Part 2 next week.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #714185

sknox

Posted Monday, May 11, 2009 11:03 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 1,040, Visits: 1,356

I can definitely see some use for this.
Even if I had no use for it at all, it's a very interesting academic exercise. I'm looking forward to seeing how you create the trigger scripts using XML. I can envision doing it in T-SQL with cursors (and I'm sure Jeff Moden could rewrite that without cursors Tongue

Post #714349

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions