Re: Update trigger
From: Palooka <nobody_at_nowhere.com>
Date: Fri, 17 Oct 2008 21:40:26 +0100
Message-ID: <_K6Kk.817$FH4.657@newsfe21.ams2>
>
> 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.
Date: Fri, 17 Oct 2008 21:40:26 +0100
Message-ID: <_K6Kk.817$FH4.657@newsfe21.ams2>
Shakespeare wrote:
> "phancey" <deja_at_2bytes.co.uk> schreef in bericht
> news:cc27d995-aa94-4b22-9dc7-bcbe57c9e9ce_at_l76g2000hse.googlegroups.com...
> On 17 Oct, 15:30, "Shakespeare" <what..._at_xs4all.nl> wrote:
>> "Ed Prochak" <edproc..._at_gmail.com> schreef in
>> berichtnews:c1a46f04-8b2b-4d34-b8fd-22de923c632e_at_34g2000hsh.googlegroups.com...
>> On Oct 17, 7:33 am, ddf <orat..._at_msn.com> wrote:
>>
>>
>>
>>> Comments embedded.
>>> On Oct 17, 5:37 am, phancey <d..._at_2bytes.co.uk> wrote:
>>>> On 17 Oct, 11:25, sybrandb <sybra..._at_gmail.com> wrote:
>>>>> On 17 okt, 12:00, phancey <d..._at_2bytes.co.uk> 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