Debian Codegraph @

mysql-test/suite/engines/funcs/r/tr_insert.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 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
8 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
9 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
10 END//
11 INSERT INTO t1(c1,c2) VALUES(11,11);
12 REPLACE INTO t1(c1,c2) VALUES(12,12);
13 REPLACE INTO t1(c1,c2) VALUES(1,11);
14 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
15 SELECT * FROM t1 ORDER BY c1;
16 c1 c2
17 1 11
18 2 2
19 3 11
20 4 11
21 6 6
22 7 7
23 8 8
24 9 9
25 10 10
26 11 11
27 12 12
28 SELECT * FROM t2 ORDER BY c1;
29 c1 old1 new1 old2 new2
30 1 0 11 0 11
31 2 0 12 0 12
32 3 0 1 0 11
33 4 0 3 0 11
34 5 0 4 0 11
35 SELECT * FROM t3 ORDER BY c1;
36 c1 c2
37 1 56
38 DROP TABLE t1,t2,t3;
39 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
40 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
41 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
42 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
43 INSERT INTO t3 VALUES(1,0);
44 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
45 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
46 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
47 END//
48 INSERT INTO t1(c1,c2) VALUES(11,11);
49 REPLACE INTO t1(c1,c2) VALUES(12,12);
50 REPLACE INTO t1(c1,c2) VALUES(1,11);
51 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
52 SELECT * FROM t1 ORDER BY c1;
53 c1 c2
54 1 11
55 2 2
56 3 11
57 4 11
58 6 6
59 7 7
60 8 8
61 9 9
62 10 10
63 11 11
64 12 12
65 SELECT * FROM t2 ORDER BY c1;
66 c1 old1 new1 old2 new2
67 1 0 11 0 11
68 2 0 12 0 12
69 3 0 1 0 11
70 4 0 3 0 11
71 5 0 4 0 11
72 SELECT * FROM t3 ORDER BY c1;
73 c1 c2
74 1 56
75 DROP TABLE t1,t2,t3;
76 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
77 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
78 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
79 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
80 INSERT INTO t3 VALUES(1,0);
81 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
82 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
83 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
84 END//
85 INSERT INTO t1(c1,c2) VALUES(11,11);
86 REPLACE INTO t1(c1,c2) VALUES(12,12);
87 REPLACE INTO t1(c1,c2) VALUES(1,11);
88 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
89 SELECT * FROM t1 ORDER BY c1;
90 c1 c2
91 1 11
92 2 2
93 3 11
94 4 11
95 6 6
96 7 7
97 8 8
98 9 9
99 10 10
100 11 11
101 12 12
102 SELECT * FROM t2 ORDER BY c1;
103 c1 old1 new1 old2 new2
104 1 0 11 0 11
105 2 0 12 0 12
106 3 0 1 0 11
107 4 0 3 0 11
108 5 0 4 0 11
109 SELECT * FROM t3 ORDER BY c1;
110 c1 c2
111 1 56
112 DROP TABLE t1,t2,t3;
113 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
114 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
115 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
116 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
117 INSERT INTO t3 VALUES(1,0);
118 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
119 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
120 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
121 END//
122 INSERT INTO t1(c1,c2) VALUES(11,11);
123 REPLACE INTO t1(c1,c2) VALUES(12,12);
124 REPLACE INTO t1(c1,c2) VALUES(1,11);
125 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
126 SELECT * FROM t1 ORDER BY c1;
127 c1 c2
128 1 11
129 2 2
130 3 11
131 4 11
132 6 6
133 7 7
134 8 8
135 9 9
136 10 10
137 11 11
138 12 12
139 SELECT * FROM t2 ORDER BY c1;
140 c1 old1 new1 old2 new2
141 1 0 11 0 11
142 2 0 12 0 12
143 3 0 1 0 11
144 4 0 3 0 11
145 5 0 4 0 11
146 SELECT * FROM t3 ORDER BY c1;
147 c1 c2
148 1 56
149 DROP TABLE t1,t2,t3;
150 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
151 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
152 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
153 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
154 INSERT INTO t3 VALUES(1,0);
155 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
156 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
157 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
158 END//
159 INSERT INTO t1(c1,c2) VALUES(11,11);
160 REPLACE INTO t1(c1,c2) VALUES(12,12);
161 REPLACE INTO t1(c1,c2) VALUES(1,11);
162 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
163 SELECT * FROM t1 ORDER BY c1;
164 c1 c2
165 1 11
166 2 2
167 3 11
168 4 11
169 6 6
170 7 7
171 8 8
172 9 9
173 10 10
174 11 11
175 12 12
176 SELECT * FROM t2 ORDER BY c1;
177 c1 old1 new1 old2 new2
178 1 0 11 0 11
179 2 0 12 0 12
180 3 0 1 0 11
181 4 0 3 0 11
182 5 0 4 0 11
183 SELECT * FROM t3 ORDER BY c1;
184 c1 c2
185 1 56
186 DROP TABLE t1,t2,t3;
187 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
188 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
189 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
190 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
191 INSERT INTO t3 VALUES(1,0);
192 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
193 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
194 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
195 END//
196 INSERT INTO t1(c1,c2) VALUES(11,11);
197 REPLACE INTO t1(c1,c2) VALUES(12,12);
198 REPLACE INTO t1(c1,c2) VALUES(1,11);
199 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
200 SELECT * FROM t1 ORDER BY c1;
201 c1 c2
202 1 11
203 2 2
204 3 11
205 4 11
206 6 6
207 7 7
208 8 8
209 9 9
210 10 10
211 11 11
212 12 12
213 SELECT * FROM t2 ORDER BY c1;
214 c1 old1 new1 old2 new2
215 1 0 11 0 11
216 2 0 12 0 12
217 3 0 1 0 11
218 4 0 3 0 11
219 5 0 4 0 11
220 SELECT * FROM t3 ORDER BY c1;
221 c1 c2
222 1 56
223 DROP TABLE t1,t2,t3;
224 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
225 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
226 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
227 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
228 INSERT INTO t3 VALUES(1,0);
229 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
230 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
231 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
232 END//
233 INSERT INTO t1(c1,c2) VALUES(11,11);
234 REPLACE INTO t1(c1,c2) VALUES(12,12);
235 REPLACE INTO t1(c1,c2) VALUES(1,11);
236 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
237 SELECT * FROM t1 ORDER BY c1;
238 c1 c2
239 1 11
240 2 2
241 3 11
242 4 11
243 6 6
244 7 7
245 8 8
246 9 9
247 10 10
248 11 11
249 12 12
250 SELECT * FROM t2 ORDER BY c1;
251 c1 old1 new1 old2 new2
252 1 0 11 0 11
253 2 0 12 0 12
254 3 0 1 0 11
255 4 0 3 0 11
256 5 0 4 0 11
257 SELECT * FROM t3 ORDER BY c1;
258 c1 c2
259 1 56
260 DROP TABLE t1,t2,t3;
261 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
262 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
263 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
264 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
265 INSERT INTO t3 VALUES(1,0);
266 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
267 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
268 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
269 END//
270 INSERT INTO t1(c1,c2) VALUES(11,11);
271 REPLACE INTO t1(c1,c2) VALUES(12,12);
272 REPLACE INTO t1(c1,c2) VALUES(1,11);
273 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
274 SELECT * FROM t1 ORDER BY c1;
275 c1 c2
276 1 11
277 2 2
278 3 11
279 4 11
280 6 6
281 7 7
282 8 8
283 9 9
284 10 10
285 11 11
286 12 12
287 SELECT * FROM t2 ORDER BY c1;
288 c1 old1 new1 old2 new2
289 1 0 11 0 11
290 2 0 12 0 12
291 3 0 1 0 11
292 4 0 3 0 11
293 5 0 4 0 11
294 SELECT * FROM t3 ORDER BY c1;
295 c1 c2
296 1 56
297 DROP TABLE t1,t2,t3;
298 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
299 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
300 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
301 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
302 INSERT INTO t3 VALUES(1,0);
303 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
304 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
305 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
306 END//
307 INSERT INTO t1(c1,c2) VALUES(11,11);
308 REPLACE INTO t1(c1,c2) VALUES(12,12);
309 REPLACE INTO t1(c1,c2) VALUES(1,11);
310 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
311 SELECT * FROM t1 ORDER BY c1;
312 c1 c2
313 1 11
314 2 2
315 3 11
316 4 11
317 6 6
318 7 7
319 8 8
320 9 9
321 10 10
322 11 11
323 12 12
324 SELECT * FROM t2 ORDER BY c1;
325 c1 old1 new1 old2 new2
326 1 0 11 0 11
327 2 0 12 0 12
328 3 0 1 0 11
329 4 0 3 0 11
330 5 0 4 0 11
331 SELECT * FROM t3 ORDER BY c1;
332 c1 c2
333 1 56
334 DROP TABLE t1,t2,t3;
335 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
336 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
337 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
338 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
339 INSERT INTO t3 VALUES(1,0);
340 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
341 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
342 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
343 END//
344 INSERT INTO t1(c1,c2) VALUES(11,11);
345 REPLACE INTO t1(c1,c2) VALUES(12,12);
346 REPLACE INTO t1(c1,c2) VALUES(1,11);
347 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
348 SELECT * FROM t1 ORDER BY c1;
349 c1 c2
350 1 11
351 2 2
352 3 11
353 4 11
354 6 6
355 7 7
356 8 8
357 9 9
358 10 10
359 11 11
360 12 12
361 SELECT * FROM t2 ORDER BY c1;
362 c1 old1 new1 old2 new2
363 1 0 11 0 11
364 2 0 12 0 12
365 3 0 1 0 11
366 4 0 3 0 11
367 5 0 4 0 11
368 SELECT * FROM t3 ORDER BY c1;
369 c1 c2
370 1 56
371 DROP TABLE t1,t2,t3;
372 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
373 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
374 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
375 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
376 INSERT INTO t3 VALUES(1,0);
377 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
378 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
379 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
380 END//
381 INSERT INTO t1(c1,c2) VALUES(11,11);
382 REPLACE INTO t1(c1,c2) VALUES(12,12);
383 REPLACE INTO t1(c1,c2) VALUES(1,11);
384 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
385 SELECT * FROM t1 ORDER BY c1;
386 c1 c2
387 1 11
388 2 2
389 3 11
390 4 11
391 6 6
392 7 7
393 8 8
394 9 9
395 10 10
396 11 11
397 12 12
398 SELECT * FROM t2 ORDER BY c1;
399 c1 old1 new1 old2 new2
400 1 0 11 0 11
401 2 0 12 0 12
402 3 0 1 0 11
403 4 0 3 0 11
404 5 0 4 0 11
405 SELECT * FROM t3 ORDER BY c1;
406 c1 c2
407 1 56
408 DROP TABLE t1,t2,t3;
409 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
410 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
411 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
412 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
413 INSERT INTO t3 VALUES(1,0);
414 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
415 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
416 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
417 END//
418 INSERT INTO t1(c1,c2) VALUES(11,11);
419 REPLACE INTO t1(c1,c2) VALUES(12,12);
420 REPLACE INTO t1(c1,c2) VALUES(1,11);
421 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
422 SELECT * FROM t1 ORDER BY c1;
423 c1 c2
424 1 11
425 2 2
426 3 11
427 4 11
428 6 6
429 7 7
430 8 8
431 9 9
432 10 10
433 11 11
434 12 12
435 SELECT * FROM t2 ORDER BY c1;
436 c1 old1 new1 old2 new2
437 1 0 11 0 11
438 2 0 12 0 12
439 3 0 1 0 11
440 4 0 3 0 11
441 5 0 4 0 11
442 SELECT * FROM t3 ORDER BY c1;
443 c1 c2
444 1 56
445 DROP TABLE t1,t2,t3;
446 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
447 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
448 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
449 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
450 INSERT INTO t3 VALUES(1,0);
451 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
452 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
453 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
454 END//
455 INSERT INTO t1(c1,c2) VALUES(11,11);
456 REPLACE INTO t1(c1,c2) VALUES(12,12);
457 REPLACE INTO t1(c1,c2) VALUES(1,11);
458 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
459 SELECT * FROM t1 ORDER BY c1;
460 c1 c2
461 1 11
462 2 2
463 3 11
464 4 11
465 6 6
466 7 7
467 8 8
468 9 9
469 10 10
470 11 11
471 12 12
472 SELECT * FROM t2 ORDER BY c1;
473 c1 old1 new1 old2 new2
474 1 0 11 0 11
475 2 0 12 0 12
476 3 0 1 0 11
477 4 0 3 0 11
478 5 0 4 0 11
479 SELECT * FROM t3 ORDER BY c1;
480 c1 c2
481 1 56
482 DROP TABLE t1,t2,t3;
483 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
484 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
485 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
486 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
487 INSERT INTO t3 VALUES(1,0);
488 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
489 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
490 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
491 END//
492 INSERT INTO t1(c1,c2) VALUES(11,11);
493 REPLACE INTO t1(c1,c2) VALUES(12,12);
494 REPLACE INTO t1(c1,c2) VALUES(1,11);
495 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
496 SELECT * FROM t1 ORDER BY c1;
497 c1 c2
498 1 11
499 2 2
500 3 11
501 4 11
502 6 6
503 7 7
504 8 8
505 9 9
506 10 10
507 11 11
508 12 12
509 SELECT * FROM t2 ORDER BY c1;
510 c1 old1 new1 old2 new2
511 1 0 11 0 11
512 2 0 12 0 12
513 3 0 1 0 11
514 4 0 3 0 11
515 5 0 4 0 11
516 SELECT * FROM t3 ORDER BY c1;
517 c1 c2
518 1 56
519 DROP TABLE t1,t2,t3;
520 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
521 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
522 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
523 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
524 INSERT INTO t3 VALUES(1,0);
525 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
526 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
527 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
528 END//
529 INSERT INTO t1(c1,c2) VALUES(11,11);
530 REPLACE INTO t1(c1,c2) VALUES(12,12);
531 REPLACE INTO t1(c1,c2) VALUES(1,11);
532 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
533 SELECT * FROM t1 ORDER BY c1;
534 c1 c2
535 1 11
536 2 2
537 3 11
538 4 11
539 6 6
540 7 7
541 8 8
542 9 9
543 10 10
544 11 11
545 12 12
546 SELECT * FROM t2 ORDER BY c1;
547 c1 old1 new1 old2 new2
548 1 0 11 0 11
549 2 0 12 0 12
550 3 0 1 0 11
551 4 0 3 0 11
552 5 0 4 0 11
553 SELECT * FROM t3 ORDER BY c1;
554 c1 c2
555 1 56
556 DROP TABLE t1,t2,t3;
557 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
558 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
559 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
560 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
561 INSERT INTO t3 VALUES(1,0);
562 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
563 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
564 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
565 END//
566 INSERT INTO t1(c1,c2) VALUES(11,11);
567 REPLACE INTO t1(c1,c2) VALUES(12,12);
568 REPLACE INTO t1(c1,c2) VALUES(1,11);
569 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
570 SELECT * FROM t1 ORDER BY c1;
571 c1 c2
572 1 11
573 2 2
574 3 11
575 4 11
576 6 6
577 7 7
578 8 8
579 9 9
580 10 10
581 11 11
582 12 12
583 SELECT * FROM t2 ORDER BY c1;
584 c1 old1 new1 old2 new2
585 1 0 11 0 11
586 2 0 12 0 12
587 3 0 1 0 11
588 4 0 3 0 11
589 5 0 4 0 11
590 SELECT * FROM t3 ORDER BY c1;
591 c1 c2
592 1 56
593 DROP TABLE t1,t2,t3;
594 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
595 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
596 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
597 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
598 INSERT INTO t3 VALUES(1,0);
599 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
600 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
601 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
602 END//
603 INSERT INTO t1(c1,c2) VALUES(11,11);
604 REPLACE INTO t1(c1,c2) VALUES(12,12);
605 REPLACE INTO t1(c1,c2) VALUES(1,11);
606 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
607 SELECT * FROM t1 ORDER BY c1;
608 c1 c2
609 1 11
610 2 2
611 3 11
612 4 11
613 6 6
614 7 7
615 8 8
616 9 9
617 10 10
618 11 11
619 12 12
620 SELECT * FROM t2 ORDER BY c1;
621 c1 old1 new1 old2 new2
622 1 0 11 0 11
623 2 0 12 0 12
624 3 0 1 0 11
625 4 0 3 0 11
626 5 0 4 0 11
627 SELECT * FROM t3 ORDER BY c1;
628 c1 c2
629 1 56
630 DROP TABLE t1,t2,t3;
631 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
632 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
633 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
634 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
635 INSERT INTO t3 VALUES(1,0);
636 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
637 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
638 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
639 END//
640 INSERT INTO t1(c1,c2) VALUES(11,11);
641 REPLACE INTO t1(c1,c2) VALUES(12,12);
642 REPLACE INTO t1(c1,c2) VALUES(1,11);
643 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
644 SELECT * FROM t1 ORDER BY c1;
645 c1 c2
646 1 11
647 2 2
648 3 11
649 4 11
650 6 6
651 7 7
652 8 8
653 9 9
654 10 10
655 11 11
656 12 12
657 SELECT * FROM t2 ORDER BY c1;
658 c1 old1 new1 old2 new2
659 1 0 11 0 11
660 2 0 12 0 12
661 3 0 1 0 11
662 4 0 3 0 11
663 5 0 4 0 11
664 SELECT * FROM t3 ORDER BY c1;
665 c1 c2
666 1 56
667 DROP TABLE t1,t2,t3;
668 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
669 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
670 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
671 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
672 INSERT INTO t3 VALUES(1,0);
673 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
674 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
675 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
676 END//
677 INSERT INTO t1(c1,c2) VALUES(11,11);
678 REPLACE INTO t1(c1,c2) VALUES(12,12);
679 REPLACE INTO t1(c1,c2) VALUES(1,11);
680 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
681 SELECT * FROM t1 ORDER BY c1;
682 c1 c2
683 1 11
684 2 2
685 3 11
686 4 11
687 6 6
688 7 7
689 8 8
690 9 9
691 10 10
692 11 11
693 12 12
694 SELECT * FROM t2 ORDER BY c1;
695 c1 old1 new1 old2 new2
696 1 0 11 0 11
697 2 0 12 0 12
698 3 0 1 0 11
699 4 0 3 0 11
700 5 0 4 0 11
701 SELECT * FROM t3 ORDER BY c1;
702 c1 c2
703 1 56
704 DROP TABLE t1,t2,t3;
705 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
706 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
707 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
708 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
709 INSERT INTO t3 VALUES(1,0);
710 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
711 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
712 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
713 END//
714 INSERT INTO t1(c1,c2) VALUES(11,11);
715 REPLACE INTO t1(c1,c2) VALUES(12,12);
716 REPLACE INTO t1(c1,c2) VALUES(1,11);
717 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
718 SELECT * FROM t1 ORDER BY c1;
719 c1 c2
720 1 11
721 2 2
722 3 11
723 4 11
724 6 6
725 7 7
726 8 8
727 9 9
728 10 10
729 11 11
730 12 12
731 SELECT * FROM t2 ORDER BY c1;
732 c1 old1 new1 old2 new2
733 1 0 11 0 11
734 2 0 12 0 12
735 3 0 1 0 11
736 4 0 3 0 11
737 5 0 4 0 11
738 SELECT * FROM t3 ORDER BY c1;
739 c1 c2
740 1 56
741 DROP TABLE t1,t2,t3;
742 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
743 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
744 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
745 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
746 INSERT INTO t3 VALUES(1,0);
747 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
748 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
749 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
750 END//
751 INSERT INTO t1(c1,c2) VALUES(11,11);
752 REPLACE INTO t1(c1,c2) VALUES(12,12);
753 REPLACE INTO t1(c1,c2) VALUES(1,11);
754 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
755 SELECT * FROM t1 ORDER BY c1;
756 c1 c2
757 1 11
758 2 2
759 3 11
760 4 11
761 6 6
762 7 7
763 8 8
764 9 9
765 10 10
766 11 11
767 12 12
768 SELECT * FROM t2 ORDER BY c1;
769 c1 old1 new1 old2 new2
770 1 0 11 0 11
771 2 0 12 0 12
772 3 0 1 0 11
773 4 0 3 0 11
774 5 0 4 0 11
775 SELECT * FROM t3 ORDER BY c1;
776 c1 c2
777 1 56
778 DROP TABLE t1,t2,t3;
779 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
780 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
781 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
782 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
783 INSERT INTO t3 VALUES(1,0);
784 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
785 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
786 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
787 END//
788 INSERT INTO t1(c1,c2) VALUES(11,11);
789 REPLACE INTO t1(c1,c2) VALUES(12,12);
790 REPLACE INTO t1(c1,c2) VALUES(1,11);
791 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
792 SELECT * FROM t1 ORDER BY c1;
793 c1 c2
794 1 11
795 2 2
796 3 11
797 4 11
798 6 6
799 7 7
800 8 8
801 9 9
802 10 10
803 11 11
804 12 12
805 SELECT * FROM t2 ORDER BY c1;
806 c1 old1 new1 old2 new2
807 1 0 11 0 11
808 2 0 12 0 12
809 3 0 1 0 11
810 4 0 3 0 11
811 5 0 4 0 11
812 SELECT * FROM t3 ORDER BY c1;
813 c1 c2
814 1 56
815 DROP TABLE t1,t2,t3;