Re: Update trigger
Date: Fri, 17 Oct 2008 07:21:36 -0700 (PDT)
Message-ID: <22e42ddc-d3aa-4d71-a118-c23e323a6675@8g2000hse.googlegroups.com>
On 17 Oct, 14:50, ddf <orat..._at_msn.com> wrote:
> On Oct 17, 8:21 am, phancey <d..._at_2bytes.co.uk> wrote:
>
>
>
> > On 17 Oct, 13:33, 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?
>
> > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > eh???
> > but I specifically said that the application updates all columns even
> > if it is only actually changing one column i.e. yes it sets existing
> > columns to their old value. That was the premise of the post and I
> > know the rest would be incorrect if this was not true.
>
> > I appreciate your efforts in helping but in doing so you do seem to be
> > slightly knocking my 'logic' and suggesting I don't know what I'm
> > talking about!! If your efforts were well intentioned, thank you. If
> > they were to show up my 'logic' then please read more carefully before
> > posting.
>
> > > > Rightly or wrongly the columns are all updated on a
> > > > record update even if we are only effectively changing one column
> > > > value
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -
I replied before I saw that but as I said, I do appreciate advice as that was what I asked for. Maybe it's because I've got a cold and feeling low anyway but things like putting "your 'logic'" and "if Oracle operated as you suggest" just sounded the wrong note with me. My apologies - text is never a great way of inferring tone but sometimes we still do ;o) Received on Fri Oct 17 2008 - 09:21:36 CDT