Re: "All triggers are evil",..., really? (the mass change audit bit)

On Wed, Sep 3, 2008 at 5:41 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> 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.)

I can't speak for Connor, but I don't think I'd disagree with what you say, though I'd suggest that the situation you describe is actually saying that the business rule is "All transactions shall be audited to ensure (outcome X)", not "On each transaction the following procedure shall be executed". The advantage of this is, as you correctly say, that appropriate auditing can happen dependent on transaction details. The disadvantage in practice is that the mass updates tend in fact to have no, or minimal control, imposed upon them. That is the standard audting is disabled and is not effectively replaced by anything. I've lost count of the number of times I've been asked "Can't you do a data fix".

Niall

>
> 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.)
>
> Regards,
>
> mwf
>
> -----Original Message-----
> <snip>
> b) it ought to apply no matter where the transaction comes from.
>
> <snip>
>
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l