Re: Update trigger
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