Re: Update trigger

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 17 Oct 2008 16:30:51 +0200
Message-ID: <48f8a19a$0$194$e4fe514c@news.xs4all.nl>

"Ed Prochak" <edprochak_at_gmail.com> schreef in bericht news: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 Received on Fri Oct 17 2008 - 09:30:51 CDT

Original text of this message