Re: Update trigger

On Oct 17, 9:30 am, "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 -

In some implementations it is a requirement to capture all 'changes' to a record (the Federal Aviation Administration here in the U.S. requires such a trail). Filtering results for a report is much easier than complicating a trigger with conditional logic to avoid 'unnecessary' history records.

David Fitzjarrell Received on Fri Oct 17 2008 - 09:50:52 CDT