Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I Expand / Collapse
Author
Message
Posted Friday, April 17, 2009 2:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 645, Visits: 1,844
Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I
Post #699195
Posted Monday, May 11, 2009 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:55 AM
Points: 6, Visits: 35
The trigger is very spiffy. Really good!

Mike V
Post #713970
Posted Monday, May 11, 2009 5:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
Posted Monday, May 11, 2009 5:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:18 AM
Points: 846, Visits: 849
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
Posted Monday, May 11, 2009 5:45 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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
Posted Monday, May 11, 2009 6:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 645, Visits: 1,844
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
Posted Monday, May 11, 2009 7:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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
Posted Monday, May 11, 2009 7:18 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 645, Visits: 1,844
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
Posted Monday, May 11, 2009 8:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
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
Posted Monday, May 11, 2009 11:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 1,293, Visits: 1,647
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 ).
Post #714349
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse