Re: Update trigger

On Oct 17, 8:21 am, phancey <d...@xxxxxxxxxxxx> wrote:
On 17 Oct, 13:33, ddf <orat...@xxxxxxx> wrote:





Comments embedded.
On Oct 17, 5:37 am, phancey <d...@xxxxxxxxxxxx> wrote:

On 17 Oct, 11:25, sybrandb <sybra...@xxxxxxxxx> wrote:

On 17 okt, 12:00, phancey <d...@xxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: Trigger for update
    ... > which 2 logins has access to modify this DB and specially EMP, ... > the trigger works successfully when I make new changes in empname ... Since you are dealing with a cross database query, ... A better way might be to use dynamic SQL to determine if the user really ...
    (microsoft.public.sqlserver.programming)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... record update even if we are only effectively changing one column ... SQL> create or replace trigger emp_upd_trig ... SQL> update emp ...
    (comp.databases.oracle.server)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> create or replace trigger emp_upd_trig ... after update of ename, job, sal on emp ... from emp where empno> 8000; ...
    (comp.databases.oracle.server)
  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ... So I figured a trigger on the ... SQL> create table inactive( ... a customer opts out of some email product. ...
    (comp.databases.oracle.misc)