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

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

  • The trigger is very spiffy. Really good!

    Mike V

  • 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 (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

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

  • 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.

  • 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.

  • 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.)

  • Nice job, David.

    Everyone look for Part 2 next week.

  • 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).

  • Nice and article and an interesting concept. I'd also be concerned about overhead and space issues. Although space issues could easily be dealt with by purging the table regularly. I can't imagine using this process to rollback something that happened a month ago.

    I look forward to article number 2.

  • Nice article. Good feature too. But would like to know how it would work with 1000's of tables on production where updates, inserts and deletes are in high volume. Wouldn't Trigger going to kill the server.

    Would wait for II nd part.

    SQL DBA.

  • David McKinney (5/11/2009)


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

    Hey David,

    Point taken - and I agree it is a good thing to experiment and do things just to see if they can be done.

    In fact I agree with all of your comments.

    If the article had said what you said in your comments, I probably wouldn't have bothered posting at all 🙂

    Paul

  • Paul White (5/11/2009)


    David McKinney (5/11/2009)


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

    Hey David,

    Point taken - and I agree it is a good thing to experiment and do things just to see if they can be done.

    In fact I agree with all of your comments.

    If the article had said what you said in your comments, I probably wouldn't have bothered posting at all 🙂

    Paul

    You're right, Paul, there is a complete lack of real world context / usage in this [Part of the] article.

    It was originally one long article (i.e. not in two parts). Unfortunately when I split it, my warnings over usage fell solely in the second part.

    Anyway, I'm glad my comments have reassured you!

  • Nice example, David. I developed a complimentary set of audit functionality that audits data changes themselves a couple years ago for SQL Server 2000. I ported it to SQL Server 2005, and would like to generalize the approach and clean up the implementation, but I understand that data-audit functionality ("Change Data Capture") is a built-in feature in 2008. So, I suspect that the life of my little custom tool for auditing data changes is near it's end.

    However, I am most interested in your follow-up example. The database I wrote data change auditing for has several hundred tables. So, automating the trigger generating code was a key challenge I faced, and the code I came up with to generate the data change audit triggers was somewhat complex and cumbersome. Since you are going to conquer a similar problem in generating these triggers, and you have hinted that you are going to use XML, it sounds like you are going to solve the same problem using a different approach. I am quite curious to say the least!

    Simon Taylor

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply