Re: Update trigger

From: Palooka <>
Date: Fri, 17 Oct 2008 21:40:26 +0100
Message-ID: <_K6Kk.817$FH4.657@newsfe21.ams2>

Shakespeare wrote:
> "phancey" <> schreef in bericht
> On 17 Oct, 15:30, "Shakespeare" <> wrote:

>> "Ed Prochak" <> schreef in 
>> On Oct 17, 7:33 am, ddf <> wrote:
>>> Comments embedded.
>>> On Oct 17, 5:37 am, phancey <> wrote:
>>>> On 17 Oct, 11:25, sybrandb <> wrote:
>>>>> On 17 okt, 12:00, phancey <> wrote:
>>>>>> hi,
>>>>>> I need to write a history record for certain fields that change on 
>>>>>> a
>>>>>> table. I am not sure whether it is better to write a single AFTER
>>>>>> UPDATE OF column1,column2,column3 etc trigger that then uses if
>>>>>> statements to check each specific field whether it has changed OR 
>>>>>> to
>>>>>> create an AFTER UPDATE OF column1 ON mytable WHEN
>>>>>> nvl(new.column1,-1)
>>>>>> <> nvl(old.column1,-1) trigger for each column for example.
>>>>>> Is there a performance hit in having 7 AFTER UPDATE triggers
>>>>>> defined,
>>>>>> one for each column I am interested in rather than 1 trigger for 
>>>>>> all
>>>>>> 7?
>>>>>> thanks for any help.
>>>>>> Phil
>>>>> Triggers are parsed on invocation. Obviously 1 parse is less 
>>>>> expensive
>>>>> than 7 parses.
>>>>> If you just use
>>>>> IF updating('<column name>' then
>>>>> you wouldn't embark on creating a performance nightmare, which looks
>>>>> syntactically incorrect too.
>>>>> --
>>>>> Sybrand Bakker
>>>>> Senior Oracle DBA- Hide quoted text -
>>>>> - Show quoted text -
>>>> yes but it is only when the value changes that I should write the
>>>> history record. Rightly or wrongly the columns are all updated on a
>>>> record update even if we are only effectively changing one column
>>>> value (no point in discussing the merits of this as my hands are tied)
>>> That's incorrect.
>>>> - not strictly true as there are a couple of instances where updates
>>>> are more targeted hence my use of a column list in the trigger
>>>> definition but certainly the update of a column does not guarantee
>>>> that it is changing.
>>> But it does guarantee the value is changing unless the SET statement
>>> uses the existing value.
>>>> IF updating('<column name>') then..... would write the history record
>>>> every time the column was updated NOT, as required, when it was
>>>> changed?
>> DAvid,
>> I think he is saying the target columns are set in the update
>> statement, but they may be set to their current value. If there is no
>> actual change, he does not want the log entry. Personally, I would
>> still want the log entry (otherwise the history is incomplete) and
>> would filter it out on any reports that did not need it.
>> <snip>
>> ========================================================
>> Good point there: if you chose to, Designer writes the 'old' AND the 'new'
>> values to the journal tables, so needs them anyway. Comparing old and new
>> values can be done while reporting, taking away any perfomance issue from
>> the actual journaling process..
>> Shakespeare- Hide quoted text -
>> - Show quoted text -

> My understanding was that if I write entries to table unnecessarily
> then that would be worse performance than doing a check beforehand.
> Both on the trigger and then in any report where it would have to
> filter out more redundant data than necessary?
> If I have 12 fields that need journalling and usually say 3 or 4 of
> them only are actually changed in any update statement (although all
> of them are updated/set) , would it be better performance to write the
> 12 records regardless? (actually it also doesn't help that our
> database modeller has not really done a great job and the "journal"
> table is also quite wide)
> cheers,
> ==================================================
> Journal tables accordig to Designer contain all of the columns in one record
> (old an new values), so if you write one record containing all columns, you
> write one record for each update. I don't think it will matter (much) how
> many columns you write. I assume at least one of the columns will have been
> updated...
> Shakespeare

This is surely the best approach most of the time. If nothing has changed, don't write a history row. If something has changed, write a history row, with all the old and new values. We have a full audit trail, and it is easy to pull out the deltas if we need to.

IMHO the last thing we want to do is write 4 rows because 4 values have changed. Write one, with a datestamp!

OP: Don't forget nulls. Not equals is not enough.

Palooka Received on Fri Oct 17 2008 - 15:40:26 CDT

Original text of this message