Re: do triggers slow updates down severly

I guess we should agree to disagree. The bottom line here is that Jack has a few options/ideas/perspectives to think about. Our implementation works well for our volume of data and business rules.

Bryan

"Russell Wolfe" <wolfer_at_peak.org> wrote in message news:ne3mqusoorsgcpuhc8p3ocjmvpckdb7649_at_4ax.com...
> Sorry, but I still hold that a trigger is more efficient. I would not
> code separate inserts or updates. You certainly use sysdate, but,
> pre-insert and pre-update triggers would simply assign it to a column:
>
> new.modified_time := sysdate;
>
> Thus, no new statements need be parsed.
>
> Also, using a trigger insures that all inserts or updates will be
> modified. This is an important consideration in large systems where
> different applications, such as a bulk load via sql*loader plus manual
> modifications via sql*forms, might be involved. Moreover, as the
> application ages and programmers come and go, the code remains
> consistent. With a trigger, the timestamp is not at the mercy of
> various programmers.
>
> All that being said, a simple timestamp is very often not enough for
> an audit trail or for effective troubleshooting. I frequently find
> that, at minimum, I also need to know what value was changed, who
> changed it, and what the prior value was. This requirement almost
> dictates a separate audit table, hence my comment about turning on
> Oracle's audit features. There is certainly a performance hit, but it
> is a necessary cost of doing business.
>
> rww
>
> On Mon, 14 Oct 2002 00:40:55 GMT, "Bryan & Sarah Baker"
> <bakers65_at_cox.net> wrote:
>
> >I disagree that a trigger is the most effective method to put a timestamp
 on
> >a record.
> >
> >If you had a table (test) with (modified_time, value, date_time) and the
> >column modified_time is subject column, use sysdate to get the answer you
> >need
> >
> >
> >update test set modified_time = sysdate ...
> >
> >Inserts into test (modified_time, value, date_time) values (sysdate,
 1234,
> >TO_DATE(10/13/2002, "MM/DD/YYYY");
> >
> >The date will default to the NLS setting in the pfile or else you will
 need
> >to format sysdate into your desired form.
> >
> >Bryan Baker
> >
> >
> >"Russell Wolfe" <wolfer_at_peak.org> wrote in message
> >news:tngbqug225eposde89iu1op6e5r19507pu_at_4ax.com...
> >> On 10 Oct 2002 07:41:04 -0700, jack.beukering_at_services.fujitsu.com
> >> (jack) wrote:
> >>
> >> >I want to use update trigger to record the timestamp, so I know the
> >> >mutation dates of records. Inserts can be handled by a default
> >> >declaration of the timestamp column.
> >> >My question is, does the performance of batch-jobs suffers serverly at
> >> >e.g. 10.000 records or do you start to notice it at 100.000 records?
> >> >
> >> >Doe somebody has any experience with such a case?
> >> >
> >> >Jack
> >>
> >> Of all the ways to timestamp a record, among many other things, a
> >> trigger is the most efficient method. A trigger isn't free, but I
> >> doubt that you'll ever notice 'severe' performance degradation.
> >> IMHO, such a timestamp is a minimal requirement in a good database
> >> design. You might check out Oracle's auditing features for ways to
> >> maintain a more complete change history.
> >>
> >> rww
> >> Russ Wolfe
> >> wolfer_at_peak.org
> >
>
> Russ Wolfe
> wolfer_at_peak.org
Received on Thu Oct 17 2002 - 05:26:46 CEST