|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Yesterday @ 6:21 AM
Points: 327,
Visits: 749
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, August 20, 2009 1:28 PM
Points: 3,
Visits: 21
|
|
The trigger is very spiffy. Really good!
Mike V
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
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
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 121,
Visits: 107
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, November 16, 2009 4:59 AM
Points: 1,212,
Visits: 578
|
|
| Be interesting to see the SQL this generates for a table insert w/ 100 or so columns...
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 6:21 AM
Points: 327,
Visits: 749
|
|
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.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 6:15 AM
Points: 1,720,
Visits: 590
|
|
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.
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 6:21 AM
Points: 327,
Visits: 749
|
|
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.)
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 4:30 PM
Points: 21,860,
Visits: 6,079
|
|
Nice job, David.
Everyone look for Part 2 next week.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 6:44 AM
Points: 167,
Visits: 202
|
|
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  ).
|
|
|
|
