Re: Update trigger
Date: Fri, 17 Oct 2008 17:15:25 +0200
Message-ID: <48f8ac0b$0$186$e4fe514c@news.xs4all.nl>
"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 Received on Fri Oct 17 2008 - 10:15:25 CDT