Re: Update trigger

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

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.
.

Relevant Pages

  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • 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: SQL BEFORE puzzle
    ... trying to figure out the justification for why a BEFORE trigger should ... On a pure SQL perspective, I am going to play devil's advocate... ... subsequent tables before the main commit would occur. ... commits to wait for the main transaction block to commit. ...
    (comp.databases.theory)
  • 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: 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)