Re: do triggers slow updates down severly

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 Mon Oct 14 2002 - 20:48:22 CEST