RE: "All triggers are evil",..., really? (the mass change audit bit)
Date: Wed, 3 Sep 2008 12:41:12 -0400
Here is why I disagree with assertion b) from the thread:
From a business auditing standpoint it is equally valid to document that a mass change was executed in accordance with some set processing rule as it is to capture detailed documentation of the row by row changes. From a verification standpoint, control totals of the changes to numeric values and various summary totals of pre-change and post-change text values may be valuable in some cases.
Any such changes should be executed under controlled conditions so that simultaneous updates circumventing the normal rules cannot be accomplished.
But when possible to be done securely, suspending detailed auditing by whatever mechanism does not contravene the purpose of auditing and should be done when it is in fact less costly from a holistic viewpoint. (Including, for examples, establishing the controlled conditions, taking the time to consider what control totals are needed to make business sense of the change, and the cost of running the before and after reports.)
Without stating an opinion about how often this situation arises other than I'm damn well sure it is not the empty set, this comes under the general wisdom of not storing dirt in Fort Knox and never spending a dime to keep track of a nickel. I'm also not attempting to make any case for or against using a trigger for auditing. This is entirely about whether it is logically consistent to normally insist on detailed change tracking but allow mass updates without detailed change tracking: My claim is that it is valid for business auditing to store the rule used to execute the transform, and that it is invalid to denigrate normally insisting on detailed tracking of changes if you suspend that rule to execute a documented mass transform.
(And yes, some medium sized bucketload of default column value options starting with the obvious one mentioned should be implemented deep in the database kernel and it is hard to understand why this has never made it to the top of the priority list. Maybe there is an aversion to just implementing it with row-by-row efficiency instead of optimizing it for sets of blocks and handling block overflow extremely efficiently. It is difficult to believe a very basic implementation would fail to be much more efficient than using a trigger or applying a post column add fix-up.)
b) it ought to apply no matter where the transaction comes from.