Debian Codegraph @

mysql-test/suite/engines/funcs/r/tr_update_after_error.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3;
2 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
3 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
4 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
5 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
6 INSERT INTO t3 VALUES(1,0);
7 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
8 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
9 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
10 SET NEW.c2=100;
11 END//
12 ERROR HY000: Updating of NEW row is not allowed in after trigger
13 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
14 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
15 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
16 SET NEW.c2=100;
17 END//
18 UPDATE t1 SET c2=10 WHERE c1=1;
19 UPDATE t1 SET c2=10 WHERE c1=0;
20 UPDATE t1 SET c2=10 WHERE c1=NULL;
21 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
22 UPDATE t1 SET c2=10 WHERE c2=3;
23 SELECT * FROM t1 ORDER BY c1;
24 c1 c2
25 1 100
26 2 2
27 3 100
28 4 4
29 6 100
30 7 100
31 8 8
32 9 9
33 10 10
34 SELECT * FROM t2 ORDER BY c1;
35 c1 old1 new1 old2 new2
36 1 1 1 1 10
37 2 6 6 6 10
38 3 7 7 7 10
39 4 3 3 3 10
40 SELECT * FROM t3 ORDER BY c1;
41 c1 c2
42 1 40
43 DROP TABLE t1,t2,t3;
44 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
45 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
46 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
47 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
48 INSERT INTO t3 VALUES(1,0);
49 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
50 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
51 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
52 SET NEW.c2=100;
53 END//
54 ERROR HY000: Updating of NEW row is not allowed in after trigger
55 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
56 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
57 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
58 SET NEW.c2=100;
59 END//
60 UPDATE t1 SET c2=10 WHERE c1=1;
61 UPDATE t1 SET c2=10 WHERE c1=0;
62 UPDATE t1 SET c2=10 WHERE c1=NULL;
63 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
64 UPDATE t1 SET c2=10 WHERE c2=3;
65 SELECT * FROM t1 ORDER BY c1;
66 c1 c2
67 1 100
68 2 2
69 3 100
70 4 4
71 6 100
72 7 100
73 8 8
74 9 9
75 10 10
76 SELECT * FROM t2 ORDER BY c1;
77 c1 old1 new1 old2 new2
78 1 1 1 1 10
79 2 6 6 6 10
80 3 7 7 7 10
81 4 3 3 3 10
82 SELECT * FROM t3 ORDER BY c1;
83 c1 c2
84 1 40
85 DROP TABLE t1,t2,t3;
86 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
87 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
88 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
89 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
90 INSERT INTO t3 VALUES(1,0);
91 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
92 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
93 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
94 SET NEW.c2=100;
95 END//
96 ERROR HY000: Updating of NEW row is not allowed in after trigger
97 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
98 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
99 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
100 SET NEW.c2=100;
101 END//
102 UPDATE t1 SET c2=10 WHERE c1=1;
103 UPDATE t1 SET c2=10 WHERE c1=0;
104 UPDATE t1 SET c2=10 WHERE c1=NULL;
105 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
106 UPDATE t1 SET c2=10 WHERE c2=3;
107 SELECT * FROM t1 ORDER BY c1;
108 c1 c2
109 1 100
110 2 2
111 3 100
112 4 4
113 6 100
114 7 100
115 8 8
116 9 9
117 10 10
118 SELECT * FROM t2 ORDER BY c1;
119 c1 old1 new1 old2 new2
120 1 1 1 1 10
121 2 6 6 6 10
122 3 7 7 7 10
123 4 3 3 3 10
124 SELECT * FROM t3 ORDER BY c1;
125 c1 c2
126 1 40
127 DROP TABLE t1,t2,t3;
128 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
129 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
130 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
131 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
132 INSERT INTO t3 VALUES(1,0);
133 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
134 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
135 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
136 SET NEW.c2=100;
137 END//
138 ERROR HY000: Updating of NEW row is not allowed in after trigger
139 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
140 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
141 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
142 SET NEW.c2=100;
143 END//
144 UPDATE t1 SET c2=10 WHERE c1=1;
145 UPDATE t1 SET c2=10 WHERE c1=0;
146 UPDATE t1 SET c2=10 WHERE c1=NULL;
147 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
148 UPDATE t1 SET c2=10 WHERE c2=3;
149 SELECT * FROM t1 ORDER BY c1;
150 c1 c2
151 1 100
152 2 2
153 3 100
154 4 4
155 6 100
156 7 100
157 8 8
158 9 9
159 10 10
160 SELECT * FROM t2 ORDER BY c1;
161 c1 old1 new1 old2 new2
162 1 1 1 1 10
163 2 6 6 6 10
164 3 7 7 7 10
165 4 3 3 3 10
166 SELECT * FROM t3 ORDER BY c1;
167 c1 c2
168 1 40
169 DROP TABLE t1,t2,t3;
170 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
171 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
172 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
173 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
174 INSERT INTO t3 VALUES(1,0);
175 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
176 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
177 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
178 SET NEW.c2=100;
179 END//
180 ERROR HY000: Updating of NEW row is not allowed in after trigger
181 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
182 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
183 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
184 SET NEW.c2=100;
185 END//
186 UPDATE t1 SET c2=10 WHERE c1=1;
187 UPDATE t1 SET c2=10 WHERE c1=0;
188 UPDATE t1 SET c2=10 WHERE c1=NULL;
189 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
190 UPDATE t1 SET c2=10 WHERE c2=3;
191 SELECT * FROM t1 ORDER BY c1;
192 c1 c2
193 1 100
194 2 2
195 3 100
196 4 4
197 6 100
198 7 100
199 8 8
200 9 9
201 10 10
202 SELECT * FROM t2 ORDER BY c1;
203 c1 old1 new1 old2 new2
204 1 1 1 1 10
205 2 6 6 6 10
206 3 7 7 7 10
207 4 3 3 3 10
208 SELECT * FROM t3 ORDER BY c1;
209 c1 c2
210 1 40
211 DROP TABLE t1,t2,t3;
212 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
213 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
214 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
215 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
216 INSERT INTO t3 VALUES(1,0);
217 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
218 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
219 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
220 SET NEW.c2=100;
221 END//
222 ERROR HY000: Updating of NEW row is not allowed in after trigger
223 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
224 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
225 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
226 SET NEW.c2=100;
227 END//
228 UPDATE t1 SET c2=10 WHERE c1=1;
229 UPDATE t1 SET c2=10 WHERE c1=0;
230 UPDATE t1 SET c2=10 WHERE c1=NULL;
231 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
232 UPDATE t1 SET c2=10 WHERE c2=3;
233 SELECT * FROM t1 ORDER BY c1;
234 c1 c2
235 1 100
236 2 2
237 3 100
238 4 4
239 6 100
240 7 100
241 8 8
242 9 9
243 10 10
244 SELECT * FROM t2 ORDER BY c1;
245 c1 old1 new1 old2 new2
246 1 1 1 1 10
247 2 6 6 6 10
248 3 7 7 7 10
249 4 3 3 3 10
250 SELECT * FROM t3 ORDER BY c1;
251 c1 c2
252 1 40
253 DROP TABLE t1,t2,t3;
254 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
255 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
256 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
257 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
258 INSERT INTO t3 VALUES(1,0);
259 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
260 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
261 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
262 SET NEW.c2=100;
263 END//
264 ERROR HY000: Updating of NEW row is not allowed in after trigger
265 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
266 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
267 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
268 SET NEW.c2=100;
269 END//
270 UPDATE t1 SET c2=10 WHERE c1=1;
271 UPDATE t1 SET c2=10 WHERE c1=0;
272 UPDATE t1 SET c2=10 WHERE c1=NULL;
273 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
274 UPDATE t1 SET c2=10 WHERE c2=3;
275 SELECT * FROM t1 ORDER BY c1;
276 c1 c2
277 1 100
278 2 2
279 3 100
280 4 4
281 6 100
282 7 100
283 8 8
284 9 9
285 10 10
286 SELECT * FROM t2 ORDER BY c1;
287 c1 old1 new1 old2 new2
288 1 1 1 1 10
289 2 6 6 6 10
290 3 7 7 7 10
291 4 3 3 3 10
292 SELECT * FROM t3 ORDER BY c1;
293 c1 c2
294 1 40
295 DROP TABLE t1,t2,t3;
296 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
297 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
298 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
299 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
300 INSERT INTO t3 VALUES(1,0);
301 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
302 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
303 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
304 SET NEW.c2=100;
305 END//
306 ERROR HY000: Updating of NEW row is not allowed in after trigger
307 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
308 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
309 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
310 SET NEW.c2=100;
311 END//
312 UPDATE t1 SET c2=10 WHERE c1=1;
313 UPDATE t1 SET c2=10 WHERE c1=0;
314 UPDATE t1 SET c2=10 WHERE c1=NULL;
315 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
316 UPDATE t1 SET c2=10 WHERE c2=3;
317 SELECT * FROM t1 ORDER BY c1;
318 c1 c2
319 1 100
320 2 2
321 3 100
322 4 4
323 6 100
324 7 100
325 8 8
326 9 9
327 10 10
328 SELECT * FROM t2 ORDER BY c1;
329 c1 old1 new1 old2 new2
330 1 1 1 1 10
331 2 6 6 6 10
332 3 7 7 7 10
333 4 3 3 3 10
334 SELECT * FROM t3 ORDER BY c1;
335 c1 c2
336 1 40
337 DROP TABLE t1,t2,t3;
338 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
339 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
340 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
341 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
342 INSERT INTO t3 VALUES(1,0);
343 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
344 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
345 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
346 SET NEW.c2=100;
347 END//
348 ERROR HY000: Updating of NEW row is not allowed in after trigger
349 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
350 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
351 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
352 SET NEW.c2=100;
353 END//
354 UPDATE t1 SET c2=10 WHERE c1=1;
355 UPDATE t1 SET c2=10 WHERE c1=0;
356 UPDATE t1 SET c2=10 WHERE c1=NULL;
357 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
358 UPDATE t1 SET c2=10 WHERE c2=3;
359 SELECT * FROM t1 ORDER BY c1;
360 c1 c2
361 1 100
362 2 2
363 3 100
364 4 4
365 6 100
366 7 100
367 8 8
368 9 9
369 10 10
370 SELECT * FROM t2 ORDER BY c1;
371 c1 old1 new1 old2 new2
372 1 1 1 1 10
373 2 6 6 6 10
374 3 7 7 7 10
375 4 3 3 3 10
376 SELECT * FROM t3 ORDER BY c1;
377 c1 c2
378 1 40
379 DROP TABLE t1,t2,t3;
380 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
381 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
382 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
383 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
384 INSERT INTO t3 VALUES(1,0);
385 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
386 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
387 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
388 SET NEW.c2=100;
389 END//
390 ERROR HY000: Updating of NEW row is not allowed in after trigger
391 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
392 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
393 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
394 SET NEW.c2=100;
395 END//
396 UPDATE t1 SET c2=10 WHERE c1=1;
397 UPDATE t1 SET c2=10 WHERE c1=0;
398 UPDATE t1 SET c2=10 WHERE c1=NULL;
399 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
400 UPDATE t1 SET c2=10 WHERE c2=3;
401 SELECT * FROM t1 ORDER BY c1;
402 c1 c2
403 1 100
404 2 2
405 3 100
406 4 4
407 6 100
408 7 100
409 8 8
410 9 9
411 10 10
412 SELECT * FROM t2 ORDER BY c1;
413 c1 old1 new1 old2 new2
414 1 1 1 1 10
415 2 6 6 6 10
416 3 7 7 7 10
417 4 3 3 3 10
418 SELECT * FROM t3 ORDER BY c1;
419 c1 c2
420 1 40
421 DROP TABLE t1,t2,t3;
422 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
423 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
424 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
425 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
426 INSERT INTO t3 VALUES(1,0);
427 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
428 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
429 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
430 SET NEW.c2=100;
431 END//
432 ERROR HY000: Updating of NEW row is not allowed in after trigger
433 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
434 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
435 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
436 SET NEW.c2=100;
437 END//
438 UPDATE t1 SET c2=10 WHERE c1=1;
439 UPDATE t1 SET c2=10 WHERE c1=0;
440 UPDATE t1 SET c2=10 WHERE c1=NULL;
441 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
442 UPDATE t1 SET c2=10 WHERE c2=3;
443 SELECT * FROM t1 ORDER BY c1;
444 c1 c2
445 1 100
446 2 2
447 3 100
448 4 4
449 6 100
450 7 100
451 8 8
452 9 9
453 10 10
454 SELECT * FROM t2 ORDER BY c1;
455 c1 old1 new1 old2 new2
456 1 1 1 1 10
457 2 6 6 6 10
458 3 7 7 7 10
459 4 3 3 3 10
460 SELECT * FROM t3 ORDER BY c1;
461 c1 c2
462 1 40
463 DROP TABLE t1,t2,t3;