Re: Update trigger
Date: Fri, 17 Oct 2008 06:50:20 -0700 (PDT)
Message-ID: <55f7cd4a-e603-4d95-9122-f7ac7e607057@p59g2000hsd.googlegroups.com>
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 (no point in discussing the merits of this as my hands are tied)- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
I have already posted that I mistook your statement regarding the application behaviour for one commenting on Oracle's method of updating data and, hopefully, clarified my original statement. What more do you want from me?
About your 'kludge': it won't find non-updated updates if the column value is not NULL to start with. To modify my example a bit:
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 if nvl(:new.ename, 'same') <> nvl(:old.ename,
'same') then
7 insert into emp_hist(empno, ename, chg_user,
chg_dt, action)
8 values (:new.empno, :new.ename, user, sysdate,
'UPDATE');
9 end if;
10 end if;
11 if updating('job') then
12 if nvl(:new.job, 'same') <> nvl(:old.job, 'same')
then
13 insert into emp_hist(empno, job, chg_user,
chg_dt, action)
14 values (:new.empno, :new.job, user, sysdate,
'UPDATE');
15 end if;
16 end if;
17 if updating('sal') then
18 if nvl(:new.sal, 0) <> nvl(:old.sal, 0) then
19 insert into emp_hist(empno, sal, chg_user,
chg_dt, action)
20 values (:new.empno, :new.sal, user, sysdate,
'UPDATE');
21 end if;
22 end if;
23 end;
24 /
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, ename = ename, job = job
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 1658.88
BING 17-OCT-08 UPDATE
8002 3317.76
BING 17-OCT-08 UPDATE
8003 2592
BING 17-OCT-08 UPDATE
8004 6168.96
BING 17-OCT-08 UPDATE
8005 2592
BING 17-OCT-08 UPDATE
8006 5909.76
BING 17-OCT-08 UPDATE
8007 5080.32
BING 17-OCT-08 UPDATE
8008 6220.8
BING 17-OCT-08 UPDATE
8009 10368
BING 17-OCT-08 UPDATE
8010 3110.4
BING 17-OCT-08 UPDATE
8011 2280.96
BING 17-OCT-08 UPDATE
EMPNO ENAME JOB SAL
CHG_USER CHG_DT ACTION
---------- ---------- --------- ----------
------------------------------ --------- ------------
8012 1969.92
BING 17-OCT-08 UPDATE
8013 6220.8
BING 17-OCT-08 UPDATE
8014 2695.68
BING 17-OCT-08 UPDATE
8015 1866.24
BING 17-OCT-08 UPDATE
8016 1658.88
BING 17-OCT-08 UPDATE
8017 3317.76
BING 17-OCT-08 UPDATE
8018 2592
BING 17-OCT-08 UPDATE
8019 6168.96
BING 17-OCT-08 UPDATE
8020 2592
BING 17-OCT-08 UPDATE
8021 5909.76
BING 17-OCT-08 UPDATE
8022 5080.32
BING 17-OCT-08 UPDATE
EMPNO ENAME JOB SAL
CHG_USER CHG_DT ACTION
---------- ---------- --------- ----------
------------------------------ --------- ------------
8023 6220.8
BING 17-OCT-08 UPDATE
8024 10368
BING 17-OCT-08 UPDATE
8025 3110.4
BING 17-OCT-08 UPDATE
8026 2280.96
BING 17-OCT-08 UPDATE
8027 1969.92
BING 17-OCT-08 UPDATE
8028 6220.8
BING 17-OCT-08 UPDATE
8029 2695.68
BING 17-OCT-08 UPDATE
29 rows selected.
SQL> I am truly hoping this helps you.
David Fitzjarrell Received on Fri Oct 17 2008 - 08:50:20 CDT