Re: Update trigger

From: phancey <deja_at_2bytes.co.uk>
Date: Fri, 17 Oct 2008 06:21:52 -0700 (PDT)
Message-ID: <fd2ef08d-bb7f-43d2-a6d5-6d7ef5e61ebc@c60g2000hsf.googlegroups.com>


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 (no point in discussing the merits of this as my hands are tied)
Received on Fri Oct 17 2008 - 08:21:52 CDT

Original text of this message