Re: Update trigger
Date: Fri, 17 Oct 2008 03:37:19 -0700 (PDT)
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) - 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.
IF updating('<column name>') then..... would write the history record every time the column was updated NOT, as required, when it was changed?
However, based on your advice, I will write only one trigger listing the columns and then use IF nvl(old.column1,-1) <>.... etc unless you have more advice?
Phil Received on Fri Oct 17 2008 - 05:37:19 CDT