Re: Update trigger

"joel garry" <joel-garry_at_home.com> schreef in bericht news:495f41c3-452a-454e-bdfa-8680b9cf6b71_at_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

========================================================
Well, I remember a piece of code that had historically (and hysterically) 
grown out of hand because somewhere the design started with two hardcoded 
values of which the designers were sure they would never change, growing up 
to a 40 or more possible values to check; the comment in the code was 
something like
/* Oh my G** I wish we had used arrays here... */

Shakespeare