Re: Update trigger

From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 17 Oct 2008 07:11:53 -0700 (PDT)
Message-ID: <c1a46f04-8b2b-4d34-b8fd-22de923c632e@34g2000hsh.googlegroups.com>


On Oct 17, 7:33 am, 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?
>

DAvid,

I think he is saying the target columns are set in the update statement, but they may be set to their current value. If there is no actual change, he does not want the log entry. Personally, I would still want the log entry (otherwise the history is incomplete) and would filter it out on any reports that did not need it. So I ran a test and Phil is correct in that the update status does not check if the column really changes. I reran your test with an update that sets sal to the same value for some rows. The trigger also was changed to log if there is an actual value change for sal.

SQL> set linesize 72
SQL> drop trigger emp_upd_trig
Trigger dropped.
SQL> drop table emp
Table dropped.
SQL> create table emp (
empno number primary key,
ename varchar2(10),

job      varchar2(10),
sal     number  )

Table created.
SQL> insert into emp
select item_id,substr(item_desc,1,10),
decode(item_desc,'test','Tester','yyy','Bossman','worker') as job, 10000+item_id from ctfsapp.cont_dump_item 106 rows created.
SQL> commit
Commit complete.
SQL> select * from emp

     EMPNO ENAME JOB SAL ---------- ---------- ---------- ----------

        90 bbbbb      worker          10090
        93 test       Tester          10093
        94 test       Tester          10094
       100 test       Tester          10100
       101 st         worker          10101
       102 ste        worker          10102
       103 STOVE      worker          10103
       104 REFRIGERAT worker          10104
       105 DISHWASHER worker          10105
        63 1234567890 worker          10063
        56 charmaine  worker          10056
        64 1234567890 worker          10064
        65 1234567890 worker          10065
        81 1234567890 worker          10081
        88 yyy        Bossman         10088
        84 23234324   worker          10084
        87 xxxxx      worker          10087
        92 hi         worker          10092
        99 stove      worker          10099
        57 microwave  worker          10057
        58 dishwasher worker          10058

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
        55 testing    worker          10055
        86 32         worker          10086
        96 test       Tester          10096
        98 stove      worker          10098
       106 beer cans  worker          10106
        29 stove      worker          10029
        30 refrigerat worker          10030
        59 dishwasher worker          10059
        60 stove      worker          10060
        61 stove      worker          10061
        62 fridge     worker          10062
        66 1234567890 worker          10066
        67 1234567890 worker          10067
        68 1234567890 worker          10068
        69 1234567890 worker          10069
        70 1234567890 worker          10070
        71 1234567890 worker          10071
        72 1234567890 worker          10072
        73 1234567890 worker          10073
        74 1234567890 worker          10074
        75 1234567890 worker          10075

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
        76 1234567890 worker          10076
        77 1234567890 worker          10077
        78 1234567890 worker          10078
        79 1234567890 worker          10079
        80 1234567890 worker          10080
        91 hello      worker          10091
       107 asdf       worker          10107
       112 asfasfsafa worker          10112
       122 DETAILED D worker          10122
       139 11241      worker          10139
       149 oops       worker          10149
       151 xxx        worker          10151
       156 3rtr       worker          10156
       157 trash      worker          10157
       161 What is th worker          10161
       116 DETAILED D worker          10116
       117 DETAILED D worker          10117
       118 DETAILED D worker          10118
       119 DETAILED D worker          10119
       120 DETAILED D worker          10120
       121 DETAILED D worker          10121

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       134 0123456789 worker          10134
       142 0123456789 worker          10142
       143 (e.g. refr worker          10143
       144 (e.g. refr worker          10144
       145 asfasfsad  worker          10145
       146 DETAILED D worker          10146
       152 a          worker          10152
       154 x          worker          10154
      1805 bbbbb      worker          11805
       108 333414     worker          10108
       109 test       Tester          10109
       113 Test dump  worker          10113
       114 asfsafasfa worker          10114
       115 DETAILED D worker          10115
       125 asfasfas   worker          10125
       135 9911048921 worker          10135
       136 13414141   worker          10136
       137 asdf       worker          10137
       138 11241      worker          10138
       140 DETAILED D worker          10140
       141 0123456789 worker          10141

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
     37996 ssss       worker          47996
     38015 testing 40 worker          48015
       147 Railroad T worker          10147
       148 asdf       worker          10148
       150 Test       worker          10150
       158 grass clip worker          10158
       124 DETAILED D worker          10124
       126 DETAILED D worker          10126
       127 refrigerat worker          10127
       128 stoves     worker          10128
       129 dishwasher worker          10129
       130 as         worker          10130
       133 dishwasher worker          10133
       153 TEST       worker          10153
       155 OK         worker          10155
       159 junk       worker          10159
       110 test       Tester          10110
       111 1134131414 worker          10111
       123 DETAILED D worker          10123
       131 refrigerat worker          10131
       132 dishwasher worker          10132

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       160 complete d worker          10160


106 rows selected.
SQL> drop table emp_hist
Table dropped.
SQL> create table emp_hist
 as select empno, ename, job, sal from emp where 0=1 Table created.
SQL> alter table emp_hist add (chg_user varchar2(30), chg_dt date, action varchar2(40))
Table altered.
SQL> create or replace trigger emp_upd_trig

   after update of ename, job, sal on emp    for each row
   begin

           if updating('ename') then
                   insert into emp_hist(empno, ename, chg_user,
chg_dt,action)
                   values (:new.empno, :new.ename, user, sysdate,
'UPDATE');
           elsif updating('job') then
                   insert into emp_hist(empno, job, chg_user, chg_dt,
action)
                   values (:new.empno, :new.job, user, sysdate,
'UPDATE');
           elsif updating('sal') then
                   insert into emp_hist(empno, sal, chg_user, chg_dt,
action)
                   values (:new.empno, :new.sal, user, sysdate,
'UPDATE');
                   IF :new.sal = :old.sal THEN
                       NULL;
                   ELSE
                       insert into emp_hist(empno, sal, chg_user,
chg_dt, action)
                       values (:new.empno, :new.sal, user, sysdate,
'SAL '||:old.sal||'->'||:new.sal);
                   END IF;
           end if;

   end;
Trigger created.
SQL> select count(*) from emp where empno>150

  COUNT(*)


        14

1 row selected.
SQL> update emp set sal = sal*1.1 where empno>150 14 rows updated.
SQL> update emp set sal = 60000 where job='Tester' 6 rows updated.
SQL> commit
Commit complete.
SQL> select empno, sal, action from emp_hist

     EMPNO SAL ACTION

---------- ---------- ----------------------------------------
       151    11166.1 UPDATE
       151    11166.1 SAL 10151->11166.1
       152    11167.2 UPDATE
       152    11167.2 SAL 10152->11167.2
       153    11168.3 UPDATE
       153    11168.3 SAL 10153->11168.3
       154    11169.4 UPDATE
       154    11169.4 SAL 10154->11169.4
       155    11170.5 UPDATE
       155    11170.5 SAL 10155->11170.5
       156    11171.6 UPDATE
       156    11171.6 SAL 10156->11171.6
       157    11172.7 UPDATE
       157    11172.7 SAL 10157->11172.7
       158    11173.8 UPDATE
       158    11173.8 SAL 10158->11173.8
       159    11174.9 UPDATE
       159    11174.9 SAL 10159->11174.9
       160      11176 UPDATE
       160      11176 SAL 10160->11176
       161    11177.1 UPDATE

     EMPNO        SAL ACTION
---------- ---------- ----------------------------------------
       161    11177.1 SAL 10161->11177.1
      1805    12985.5 UPDATE
      1805    12985.5 SAL 11805->12985.5
     37996    52795.6 UPDATE
     37996    52795.6 SAL 47996->52795.6
     38015    52816.5 UPDATE
     38015    52816.5 SAL 48015->52816.5
        93      60000 UPDATE
        93      60000 SAL 10093->60000
        94      60000 UPDATE
        94      60000 SAL 10094->60000
       100      60000 UPDATE
       100      60000 SAL 10100->60000
        96      60000 UPDATE
        96      60000 SAL 10096->60000
       109      60000 UPDATE
       109      60000 SAL 10109->60000
       110      60000 UPDATE
       110      60000 SAL 10110->60000


40 rows selected.
SQL> update emp set sal = 60000 where job='Tester' 6 rows updated.
SQL> commit
Commit complete.
SQL> select empno, sal, action from emp_hist

     EMPNO SAL ACTION

---------- ---------- ----------------------------------------
       151    11166.1 UPDATE
       151    11166.1 SAL 10151->11166.1
       152    11167.2 UPDATE
       152    11167.2 SAL 10152->11167.2
       153    11168.3 UPDATE
       153    11168.3 SAL 10153->11168.3
       154    11169.4 UPDATE
       154    11169.4 SAL 10154->11169.4
       155    11170.5 UPDATE
       155    11170.5 SAL 10155->11170.5
       156    11171.6 UPDATE
       156    11171.6 SAL 10156->11171.6
       157    11172.7 UPDATE
       157    11172.7 SAL 10157->11172.7
       158    11173.8 UPDATE
       158    11173.8 SAL 10158->11173.8
       159    11174.9 UPDATE
       159    11174.9 SAL 10159->11174.9
       160      11176 UPDATE
       160      11176 SAL 10160->11176
       161    11177.1 UPDATE

     EMPNO        SAL ACTION
---------- ---------- ----------------------------------------
       161    11177.1 SAL 10161->11177.1
      1805    12985.5 UPDATE
      1805    12985.5 SAL 11805->12985.5
     37996    52795.6 UPDATE
     37996    52795.6 SAL 47996->52795.6
     38015    52816.5 UPDATE
     38015    52816.5 SAL 48015->52816.5
        93      60000 UPDATE
        93      60000 SAL 10093->60000
        94      60000 UPDATE
        94      60000 SAL 10094->60000
       100      60000 UPDATE
       100      60000 SAL 10100->60000
        96      60000 UPDATE
        96      60000 SAL 10096->60000
       109      60000 UPDATE
       109      60000 SAL 10109->60000
       110      60000 UPDATE
       110      60000 SAL 10110->60000
        93      60000 UPDATE
        94      60000 UPDATE

     EMPNO        SAL ACTION
---------- ---------- ----------------------------------------
       100      60000 UPDATE
        96      60000 UPDATE
       109      60000 UPDATE
       110      60000 UPDATE


46 rows selected. Received on Fri Oct 17 2008 - 09:11:53 CDT

Original text of this message