SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
David McKinney
David McKinney
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2057 Visits: 2090
Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I
Mike Vassalotti
Mike Vassalotti
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 35
The trigger is very spiffy. Really good!

Mike V
Paul White
Paul White
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25404 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
ronmoses
ronmoses
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 1011
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

SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2547 Visits: 711
Be interesting to see the SQL this generates for a table insert w/ 100 or so columns...
David McKinney
David McKinney
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2057 Visits: 2090
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.
Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2157 Visits: 798
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.
David McKinney
David McKinney
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2057 Visits: 2090
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.)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: Administrators
Points: 106243 Visits: 19332
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
My Blog: www.voiceofthedba.com
sknox
sknox
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3377 Visits: 2891
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 :-P).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search