Re: Update trigger
Date: Mon, 20 Oct 2008 12:31:18 +0200
Message-ID: <48fc5dfe$0$189$e4fe514c@news.xs4all.nl>
"phancey" <deja_at_2bytes.co.uk> schreef in bericht
news:1479e562-3617-4427-aa41-1c4d2e2b4975_at_a1g2000hsb.googlegroups.com...
On 17 Oct, 21:40, Palooka <nob..._at_nowhere.com> wrote:
> Shakespeare wrote:
> > "phancey" <d..._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- Hide quoted text -
>
> - Show quoted text -
in my case, the "designers" have already decided to write one record per field change. I don't particularly see a problem with this where the history record is narrow and the original records is quite wide (but again, in my case, the designers have included a whole range of superfluous fields in the history record making this logic redundant). Anyway, despite advising them otherwise, these are the constraints I have to work within ;o)
And I had already accounted for nulls by using nvl function (hadn't I?)
Well, if the designers decided so, you have no choice... but to get better designers next time ;-)
Shakespeare Received on Mon Oct 20 2008 - 05:31:18 CDT