Re: Update trigger
Date: Fri, 17 Oct 2008 05:33:12 -0700 (PDT)
Message-ID: <65dcb9c1-d5cc-4403-9206-4879ab8aedbf@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 Received on Fri Oct 17 2008 - 07:33:12 CDT