Re: Update trigger

From: phancey <deja_at_2bytes.co.uk>
Date: Fri, 17 Oct 2008 06:26:29 -0700 (PDT)
Message-ID: <ca6281ce-0d7e-4e68-8b18-a03a8e013ac0@e17g2000hsg.googlegroups.com>


On 17 Oct, 13:46, "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 -

cool - just did a search on "journalling trigger" and didn't find anything obviously useful. Can you point me in the right direction or tell me how to do this? Is this easier/more efficient than just checking old.value against new.value?

thanks Received on Fri Oct 17 2008 - 08:26:29 CDT

Original text of this message