Re: Update trigger

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, Received on Fri Oct 17 2008 - 09:50:44 CDT