Re: Update trigger

From: joel garry <joel-garry_at_home.com>
Date: Fri, 17 Oct 2008 14:29:38 -0700 (PDT)
Message-ID: <495f41c3-452a-454e-bdfa-8680b9cf6b71@b31g2000prf.googlegroups.com>


On Oct 17, 6:01 am, ddf <orat..._at_msn.com> wrote:
> On Oct 17, 7:46 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
>
>
>
>
>
> > "ddf" <orat..._at_msn.com> schreef in berichtnews:65dcb9c1-d5cc-4403-9206-4879ab8aedbf_at_f63g2000hsf.googlegroups.com...
> > 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?
>
> > Sorry, no, it doesn't.  By your 'logic' if I execute an single-column
> > update you contend that ALL columns in that record are updated, those
> > NOT listed in the SET statement updated with their exact same values.
> > This is NOT true as the following test proves:
>
> > SQL> create table emp_hist
> >   2  as select empno, ename, job, sal from emp where 0=1;
>
> > Table created.
>
> > SQL>
> > SQL> alter table emp_hist add (chg_user varchar2(30), chg_dt date,
> > action  varchar2(12));
>
> > Table altered.
>
> > SQL>
> > SQL> create or replace trigger emp_upd_trig
> >   2  after update of ename, job, sal on emp
> >   3  for each row
> >   4  begin
> >   5       if updating('ename') then
> >   6       insert into emp_hist(empno, ename, chg_user, chg_dt,
> > action)
> >   7       values (:new.empno, :new.ename, user, sysdate, 'UPDATE');
> >   8       elsif updating('job') then
> >   9       insert into emp_hist(empno, job, chg_user, chg_dt, action)
> >  10       values (:new.empno, :new.job, user, sysdate, 'UPDATE');
> >  11       elsif updating('sal') then
> >  12       insert into emp_hist(empno, sal, chg_user, chg_dt, action)
> >  13       values (:new.empno, :new.sal, user, sysdate, 'UPDATE');
> >  14       end if;
> >  15  end;
> >  16  /
>
> > Trigger created.
>
> > SQL>
> > SQL> show errors
> > No errors.
> > SQL>
> > SQL> select count(*)
> >   2  from emp where empno > 8000;
>
> >   COUNT(*)
> > ----------
> >         29
>
> > SQL>
> > SQL> update emp
> >   2  set sal = sal*1.2
> >   3  where empno > 8000;
>
> > 29 rows updated.
>
> > SQL>
> > SQL> select count(*)
> >   2  from emp_hist;
>
> >   COUNT(*)
> > ----------
> >         29
>
> > SQL>
> > SQL> select *
> >   2  from emp_hist;
>
> >      EMPNO ENAME      JOB              SAL
> > CHG_USER                       CHG_DT    ACTION
> > ---------- ---------- --------- ----------
> > ------------------------------ --------- ------------
> >       8001                            1152
> > BING                           17-OCT-08 UPDATE
> >       8002                            2304
> > BING                           17-OCT-08 UPDATE
> >       8003                            1800
> > BING                           17-OCT-08 UPDATE
> >       8004                            4284
> > BING                           17-OCT-08 UPDATE
> >       8005                            1800
> > BING                           17-OCT-08 UPDATE
> >       8006                            4104
> > BING                           17-OCT-08 UPDATE
> >       8007                            3528
> > BING                           17-OCT-08 UPDATE
> >       8008                            4320
> > BING                           17-OCT-08 UPDATE
> >       8009                            7200
> > BING                           17-OCT-08 UPDATE
> >       8010                            2160
> > BING                           17-OCT-08 UPDATE
> >       8011                            1584
> > BING                           17-OCT-08 UPDATE
>
> >      EMPNO ENAME      JOB              SAL
> > CHG_USER                       CHG_DT    ACTION
> > ---------- ---------- --------- ----------
> > ------------------------------ --------- ------------
> >       8012                            1368
> > BING                           17-OCT-08 UPDATE
> >       8013                            4320
> > BING                           17-OCT-08 UPDATE
> >       8014                            1872
> > BING                           17-OCT-08 UPDATE
> >       8015                            1296
> > BING                           17-OCT-08 UPDATE
> >       8016                            1152
> > BING                           17-OCT-08 UPDATE
> >       8017                            2304
> > BING                           17-OCT-08 UPDATE
> >       8018                            1800
> > BING                           17-OCT-08 UPDATE
> >       8019                            4284
> > BING                           17-OCT-08 UPDATE
> >       8020                            1800
> > BING                           17-OCT-08 UPDATE
> >       8021                            4104
> > BING                           17-OCT-08 UPDATE
> >       8022                            3528
> > BING                           17-OCT-08 UPDATE
>
> >      EMPNO ENAME      JOB              SAL
> > CHG_USER                       CHG_DT    ACTION
> > ---------- ---------- --------- ----------
> > ------------------------------ --------- ------------
> >       8023                            4320
> > BING                           17-OCT-08 UPDATE
> >       8024                            7200
> > BING                           17-OCT-08 UPDATE
> >       8025                            2160
> > BING                           17-OCT-08 UPDATE
> >       8026                            1584
> > BING                           17-OCT-08 UPDATE
> >       8027                            1368
> > BING                           17-OCT-08 UPDATE
> >       8028                            4320
> > BING                           17-OCT-08 UPDATE
> >       8029                            1872
> > BING                           17-OCT-08 UPDATE
>
> > 29 rows selected.
>
> > SQL>
>
> > If Oracle operated as you suggest there would be 87 rows in emp_hist,
> > 29 for each column being monitored.  Notice the only records written
> > to the history table are the SAL records, the ONLY column being
> > updated in that statement.
>
> > > 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?
>
> > You don't need that kludge, as proven above.
>
> > > thanks
> > > Phil- Hide quoted text -
>
> > > - Show quoted text -
>
> > David Fitzjarrell
>
> > ===========================================================================­­=
> > The update behaviour depends entirely on the used software. Some programs,
> > after just changing one column value,  update all the columns, even  if not
> > changed at all (i.e. put all the columns in the 'set' list). Even Oracle
> > Forms does so, unless you set the parameter 'update changed columns only'.
> > So the remark 'That's incorrect' is incorrect in this case.
> > If a program behaves like this, you'll need to check the changes in the
> > journalling trigger.
>
> > Shakespeare- Hide quoted text -
>
> > - Show quoted text -
>
> I appreciate the update on possible external application behaviour.  I
> wasn't expecting such, but I should have given the plethora of
> abyssmal code generated by applications I have seen over the years.
> Still, it does prove my point that to be considered 'updated' in the
> trigger the column must be listed in the SET statement.  That some
> programs update columns with existing values for absolutely no reason
> is ... silly (my opinion).
>
> David Fitzjarrell

Some are sillier. Within the past few days I had an issue where users were complaining that discounts on order lines were disappearing at infrequent random times. Digging deep into some complicated code, I discovered things like rows being saved into memory constructs, deleted from the db, then manipulated and re-added (with insufficent error checking). This is an actual comment from the vendor-supplied code:

"note: the code under here is pretty bad, needs a huge re-write"

Of course, that was a wild-goose chase, the actual problem was elsewhere. However, this all illustrates that merely having silly code, for whatever reason, imposes additional costs.

Including making people grumpy on cdos.

jg

--
@home.com is bogus.
One guy with a web page _can_ make a difference in people's lives.
Scott Chatfield, I salute you ( http://keep760.org/ ).
http://www.signonsandiego.com/uniontrib/20081017/news_1n17code.html
Received on Fri Oct 17 2008 - 16:29:38 CDT

Original text of this message