Debian Codegraph @

mysql-test/suite/engines/funcs/r/tr_delete.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 DELETE ON t1 FOR EACH ROW BEGIN
8 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
9 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
10 END//
11 REPLACE INTO t1(c1,c2) VALUES(12,12);
12 REPLACE INTO t1(c1,c2) VALUES(1,11);
13 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
14 SELECT * FROM t1 ORDER BY c1;
15 c1 c2
16 1 11
17 2 2
18 3 11
19 4 11
20 6 6
21 7 7
22 8 8
23 9 9
24 10 10
25 12 12
26 SELECT * FROM t2 ORDER BY c1;
27 c1 old1 new1 old2 new2
28 1 1 0 1 0
29 2 3 0 3 0
30 3 4 0 4 0
31 SELECT * FROM t3 ORDER BY c1;
32 c1 c2
33 1 8
34 DELETE FROM t1 WHERE c1=1;
35 DELETE FROM t1 WHERE c1=0;
36 DELETE FROM t1 WHERE c1=NULL;
37 DELETE FROM t1 WHERE c1=6 OR c1=7;
38 DELETE FROM t1 WHERE c2=3;
39 SELECT * FROM t1 ORDER BY c1;
40 c1 c2
41 2 2
42 3 11
43 4 11
44 8 8
45 9 9
46 10 10
47 12 12
48 SELECT * FROM t2 ORDER BY c1;
49 c1 old1 new1 old2 new2
50 1 1 0 1 0
51 2 3 0 3 0
52 3 4 0 4 0
53 4 1 0 11 0
54 5 6 0 6 0
55 6 7 0 7 0
56 SELECT * FROM t3 ORDER BY c1;
57 c1 c2
58 1 32
59 DROP TABLE t1,t2,t3;
60 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
61 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
62 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
63 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
64 INSERT INTO t3 VALUES(1,0);
65 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
66 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
67 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
68 END//
69 REPLACE INTO t1(c1,c2) VALUES(12,12);
70 REPLACE INTO t1(c1,c2) VALUES(1,11);
71 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
72 SELECT * FROM t1 ORDER BY c1;
73 c1 c2
74 1 11
75 2 2
76 3 11
77 4 11
78 6 6
79 7 7
80 8 8
81 9 9
82 10 10
83 12 12
84 SELECT * FROM t2 ORDER BY c1;
85 c1 old1 new1 old2 new2
86 1 1 0 1 0
87 2 3 0 3 0
88 3 4 0 4 0
89 SELECT * FROM t3 ORDER BY c1;
90 c1 c2
91 1 8
92 DELETE FROM t1 WHERE c1=1;
93 DELETE FROM t1 WHERE c1=0;
94 DELETE FROM t1 WHERE c1=NULL;
95 DELETE FROM t1 WHERE c1=6 OR c1=7;
96 DELETE FROM t1 WHERE c2=3;
97 SELECT * FROM t1 ORDER BY c1;
98 c1 c2
99 2 2
100 3 11
101 4 11
102 8 8
103 9 9
104 10 10
105 12 12
106 SELECT * FROM t2 ORDER BY c1;
107 c1 old1 new1 old2 new2
108 1 1 0 1 0
109 2 3 0 3 0
110 3 4 0 4 0
111 4 1 0 11 0
112 5 6 0 6 0
113 6 7 0 7 0
114 SELECT * FROM t3 ORDER BY c1;
115 c1 c2
116 1 32
117 DROP TABLE t1,t2,t3;
118 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
119 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
120 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
121 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
122 INSERT INTO t3 VALUES(1,0);
123 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
124 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
125 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
126 END//
127 REPLACE INTO t1(c1,c2) VALUES(12,12);
128 REPLACE INTO t1(c1,c2) VALUES(1,11);
129 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
130 SELECT * FROM t1 ORDER BY c1;
131 c1 c2
132 1 11
133 2 2
134 3 11
135 4 11
136 6 6
137 7 7
138 8 8
139 9 9
140 10 10
141 12 12
142 SELECT * FROM t2 ORDER BY c1;
143 c1 old1 new1 old2 new2
144 1 1 0 1 0
145 2 3 0 3 0
146 3 4 0 4 0
147 SELECT * FROM t3 ORDER BY c1;
148 c1 c2
149 1 8
150 DELETE FROM t1 WHERE c1=1;
151 DELETE FROM t1 WHERE c1=0;
152 DELETE FROM t1 WHERE c1=NULL;
153 DELETE FROM t1 WHERE c1=6 OR c1=7;
154 DELETE FROM t1 WHERE c2=3;
155 SELECT * FROM t1 ORDER BY c1;
156 c1 c2
157 2 2
158 3 11
159 4 11
160 8 8
161 9 9
162 10 10
163 12 12
164 SELECT * FROM t2 ORDER BY c1;
165 c1 old1 new1 old2 new2
166 1 1 0 1 0
167 2 3 0 3 0
168 3 4 0 4 0
169 4 1 0 11 0
170 5 6 0 6 0
171 6 7 0 7 0
172 SELECT * FROM t3 ORDER BY c1;
173 c1 c2
174 1 32
175 DROP TABLE t1,t2,t3;
176 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
177 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
178 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
179 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
180 INSERT INTO t3 VALUES(1,0);
181 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
182 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
183 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
184 END//
185 REPLACE INTO t1(c1,c2) VALUES(12,12);
186 REPLACE INTO t1(c1,c2) VALUES(1,11);
187 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
188 SELECT * FROM t1 ORDER BY c1;
189 c1 c2
190 1 11
191 2 2
192 3 11
193 4 11
194 6 6
195 7 7
196 8 8
197 9 9
198 10 10
199 12 12
200 SELECT * FROM t2 ORDER BY c1;
201 c1 old1 new1 old2 new2
202 1 1 0 1 0
203 2 3 0 3 0
204 3 4 0 4 0
205 SELECT * FROM t3 ORDER BY c1;
206 c1 c2
207 1 8
208 DELETE FROM t1 WHERE c1=1;
209 DELETE FROM t1 WHERE c1=0;
210 DELETE FROM t1 WHERE c1=NULL;
211 DELETE FROM t1 WHERE c1=6 OR c1=7;
212 DELETE FROM t1 WHERE c2=3;
213 SELECT * FROM t1 ORDER BY c1;
214 c1 c2
215 2 2
216 3 11
217 4 11
218 8 8
219 9 9
220 10 10
221 12 12
222 SELECT * FROM t2 ORDER BY c1;
223 c1 old1 new1 old2 new2
224 1 1 0 1 0
225 2 3 0 3 0
226 3 4 0 4 0
227 4 1 0 11 0
228 5 6 0 6 0
229 6 7 0 7 0
230 SELECT * FROM t3 ORDER BY c1;
231 c1 c2
232 1 32
233 DROP TABLE t1,t2,t3;
234 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
235 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
236 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
237 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
238 INSERT INTO t3 VALUES(1,0);
239 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
240 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
241 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
242 END//
243 REPLACE INTO t1(c1,c2) VALUES(12,12);
244 REPLACE INTO t1(c1,c2) VALUES(1,11);
245 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
246 SELECT * FROM t1 ORDER BY c1;
247 c1 c2
248 1 11
249 2 2
250 3 11
251 4 11
252 6 6
253 7 7
254 8 8
255 9 9
256 10 10
257 12 12
258 SELECT * FROM t2 ORDER BY c1;
259 c1 old1 new1 old2 new2
260 1 1 0 1 0
261 2 3 0 3 0
262 3 4 0 4 0
263 SELECT * FROM t3 ORDER BY c1;
264 c1 c2
265 1 8
266 DELETE FROM t1 WHERE c1=1;
267 DELETE FROM t1 WHERE c1=0;
268 DELETE FROM t1 WHERE c1=NULL;
269 DELETE FROM t1 WHERE c1=6 OR c1=7;
270 DELETE FROM t1 WHERE c2=3;
271 SELECT * FROM t1 ORDER BY c1;
272 c1 c2
273 2 2
274 3 11
275 4 11
276 8 8
277 9 9
278 10 10
279 12 12
280 SELECT * FROM t2 ORDER BY c1;
281 c1 old1 new1 old2 new2
282 1 1 0 1 0
283 2 3 0 3 0
284 3 4 0 4 0
285 4 1 0 11 0
286 5 6 0 6 0
287 6 7 0 7 0
288 SELECT * FROM t3 ORDER BY c1;
289 c1 c2
290 1 32
291 DROP TABLE t1,t2,t3;
292 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
293 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
294 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
295 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
296 INSERT INTO t3 VALUES(1,0);
297 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
298 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
299 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
300 END//
301 REPLACE INTO t1(c1,c2) VALUES(12,12);
302 REPLACE INTO t1(c1,c2) VALUES(1,11);
303 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
304 SELECT * FROM t1 ORDER BY c1;
305 c1 c2
306 1 11
307 2 2
308 3 11
309 4 11
310 6 6
311 7 7
312 8 8
313 9 9
314 10 10
315 12 12
316 SELECT * FROM t2 ORDER BY c1;
317 c1 old1 new1 old2 new2
318 1 1 0 1 0
319 2 3 0 3 0
320 3 4 0 4 0
321 SELECT * FROM t3 ORDER BY c1;
322 c1 c2
323 1 8
324 DELETE FROM t1 WHERE c1=1;
325 DELETE FROM t1 WHERE c1=0;
326 DELETE FROM t1 WHERE c1=NULL;
327 DELETE FROM t1 WHERE c1=6 OR c1=7;
328 DELETE FROM t1 WHERE c2=3;
329 SELECT * FROM t1 ORDER BY c1;
330 c1 c2
331 2 2
332 3 11
333 4 11
334 8 8
335 9 9
336 10 10
337 12 12
338 SELECT * FROM t2 ORDER BY c1;
339 c1 old1 new1 old2 new2
340 1 1 0 1 0
341 2 3 0 3 0
342 3 4 0 4 0
343 4 1 0 11 0
344 5 6 0 6 0
345 6 7 0 7 0
346 SELECT * FROM t3 ORDER BY c1;
347 c1 c2
348 1 32
349 DROP TABLE t1,t2,t3;
350 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
351 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
352 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
353 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
354 INSERT INTO t3 VALUES(1,0);
355 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
356 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
357 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
358 END//
359 REPLACE INTO t1(c1,c2) VALUES(12,12);
360 REPLACE INTO t1(c1,c2) VALUES(1,11);
361 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
362 SELECT * FROM t1 ORDER BY c1;
363 c1 c2
364 1 11
365 2 2
366 3 11
367 4 11
368 6 6
369 7 7
370 8 8
371 9 9
372 10 10
373 12 12
374 SELECT * FROM t2 ORDER BY c1;
375 c1 old1 new1 old2 new2
376 1 1 0 1 0
377 2 3 0 3 0
378 3 4 0 4 0
379 SELECT * FROM t3 ORDER BY c1;
380 c1 c2
381 1 8
382 DELETE FROM t1 WHERE c1=1;
383 DELETE FROM t1 WHERE c1=0;
384 DELETE FROM t1 WHERE c1=NULL;
385 DELETE FROM t1 WHERE c1=6 OR c1=7;
386 DELETE FROM t1 WHERE c2=3;
387 SELECT * FROM t1 ORDER BY c1;
388 c1 c2
389 2 2
390 3 11
391 4 11
392 8 8
393 9 9
394 10 10
395 12 12
396 SELECT * FROM t2 ORDER BY c1;
397 c1 old1 new1 old2 new2
398 1 1 0 1 0
399 2 3 0 3 0
400 3 4 0 4 0
401 4 1 0 11 0
402 5 6 0 6 0
403 6 7 0 7 0
404 SELECT * FROM t3 ORDER BY c1;
405 c1 c2
406 1 32
407 DROP TABLE t1,t2,t3;
408 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
409 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
410 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
411 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
412 INSERT INTO t3 VALUES(1,0);
413 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
414 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
415 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
416 END//
417 REPLACE INTO t1(c1,c2) VALUES(12,12);
418 REPLACE INTO t1(c1,c2) VALUES(1,11);
419 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
420 SELECT * FROM t1 ORDER BY c1;
421 c1 c2
422 1 11
423 2 2
424 3 11
425 4 11
426 6 6
427 7 7
428 8 8
429 9 9
430 10 10
431 12 12
432 SELECT * FROM t2 ORDER BY c1;
433 c1 old1 new1 old2 new2
434 1 1 0 1 0
435 2 3 0 3 0
436 3 4 0 4 0
437 SELECT * FROM t3 ORDER BY c1;
438 c1 c2
439 1 8
440 DELETE FROM t1 WHERE c1=1;
441 DELETE FROM t1 WHERE c1=0;
442 DELETE FROM t1 WHERE c1=NULL;
443 DELETE FROM t1 WHERE c1=6 OR c1=7;
444 DELETE FROM t1 WHERE c2=3;
445 SELECT * FROM t1 ORDER BY c1;
446 c1 c2
447 2 2
448 3 11
449 4 11
450 8 8
451 9 9
452 10 10
453 12 12
454 SELECT * FROM t2 ORDER BY c1;
455 c1 old1 new1 old2 new2
456 1 1 0 1 0
457 2 3 0 3 0
458 3 4 0 4 0
459 4 1 0 11 0
460 5 6 0 6 0
461 6 7 0 7 0
462 SELECT * FROM t3 ORDER BY c1;
463 c1 c2
464 1 32
465 DROP TABLE t1,t2,t3;
466 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
467 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
468 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
469 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
470 INSERT INTO t3 VALUES(1,0);
471 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
472 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
473 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
474 END//
475 REPLACE INTO t1(c1,c2) VALUES(12,12);
476 REPLACE INTO t1(c1,c2) VALUES(1,11);
477 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
478 SELECT * FROM t1 ORDER BY c1;
479 c1 c2
480 1 11
481 2 2
482 3 11
483 4 11
484 6 6
485 7 7
486 8 8
487 9 9
488 10 10
489 12 12
490 SELECT * FROM t2 ORDER BY c1;
491 c1 old1 new1 old2 new2
492 1 1 0 1 0
493 2 3 0 3 0
494 3 4 0 4 0
495 SELECT * FROM t3 ORDER BY c1;
496 c1 c2
497 1 8
498 DELETE FROM t1 WHERE c1=1;
499 DELETE FROM t1 WHERE c1=0;
500 DELETE FROM t1 WHERE c1=NULL;
501 DELETE FROM t1 WHERE c1=6 OR c1=7;
502 DELETE FROM t1 WHERE c2=3;
503 SELECT * FROM t1 ORDER BY c1;
504 c1 c2
505 2 2
506 3 11
507 4 11
508 8 8
509 9 9
510 10 10
511 12 12
512 SELECT * FROM t2 ORDER BY c1;
513 c1 old1 new1 old2 new2
514 1 1 0 1 0
515 2 3 0 3 0
516 3 4 0 4 0
517 4 1 0 11 0
518 5 6 0 6 0
519 6 7 0 7 0
520 SELECT * FROM t3 ORDER BY c1;
521 c1 c2
522 1 32
523 DROP TABLE t1,t2,t3;
524 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
525 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
526 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
527 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
528 INSERT INTO t3 VALUES(1,0);
529 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
530 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
531 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
532 END//
533 REPLACE INTO t1(c1,c2) VALUES(12,12);
534 REPLACE INTO t1(c1,c2) VALUES(1,11);
535 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
536 SELECT * FROM t1 ORDER BY c1;
537 c1 c2
538 1 11
539 2 2
540 3 11
541 4 11
542 6 6
543 7 7
544 8 8
545 9 9
546 10 10
547 12 12
548 SELECT * FROM t2 ORDER BY c1;
549 c1 old1 new1 old2 new2
550 1 1 0 1 0
551 2 3 0 3 0
552 3 4 0 4 0
553 SELECT * FROM t3 ORDER BY c1;
554 c1 c2
555 1 8
556 DELETE FROM t1 WHERE c1=1;
557 DELETE FROM t1 WHERE c1=0;
558 DELETE FROM t1 WHERE c1=NULL;
559 DELETE FROM t1 WHERE c1=6 OR c1=7;
560 DELETE FROM t1 WHERE c2=3;
561 SELECT * FROM t1 ORDER BY c1;
562 c1 c2
563 2 2
564 3 11
565 4 11
566 8 8
567 9 9
568 10 10
569 12 12
570 SELECT * FROM t2 ORDER BY c1;
571 c1 old1 new1 old2 new2
572 1 1 0 1 0
573 2 3 0 3 0
574 3 4 0 4 0
575 4 1 0 11 0
576 5 6 0 6 0
577 6 7 0 7 0
578 SELECT * FROM t3 ORDER BY c1;
579 c1 c2
580 1 32
581 DROP TABLE t1,t2,t3;
582 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
583 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
584 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
585 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
586 INSERT INTO t3 VALUES(1,0);
587 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
588 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
589 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
590 END//
591 REPLACE INTO t1(c1,c2) VALUES(12,12);
592 REPLACE INTO t1(c1,c2) VALUES(1,11);
593 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
594 SELECT * FROM t1 ORDER BY c1;
595 c1 c2
596 1 11
597 2 2
598 3 11
599 4 11
600 6 6
601 7 7
602 8 8
603 9 9
604 10 10
605 12 12
606 SELECT * FROM t2 ORDER BY c1;
607 c1 old1 new1 old2 new2
608 1 1 0 1 0
609 2 3 0 3 0
610 3 4 0 4 0
611 SELECT * FROM t3 ORDER BY c1;
612 c1 c2
613 1 8
614 DELETE FROM t1 WHERE c1=1;
615 DELETE FROM t1 WHERE c1=0;
616 DELETE FROM t1 WHERE c1=NULL;
617 DELETE FROM t1 WHERE c1=6 OR c1=7;
618 DELETE FROM t1 WHERE c2=3;
619 SELECT * FROM t1 ORDER BY c1;
620 c1 c2
621 2 2
622 3 11
623 4 11
624 8 8
625 9 9
626 10 10
627 12 12
628 SELECT * FROM t2 ORDER BY c1;
629 c1 old1 new1 old2 new2
630 1 1 0 1 0
631 2 3 0 3 0
632 3 4 0 4 0
633 4 1 0 11 0
634 5 6 0 6 0
635 6 7 0 7 0
636 SELECT * FROM t3 ORDER BY c1;
637 c1 c2
638 1 32
639 DROP TABLE t1,t2,t3;
640 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
641 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
642 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
643 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
644 INSERT INTO t3 VALUES(1,0);
645 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
646 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
647 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
648 END//
649 REPLACE INTO t1(c1,c2) VALUES(12,12);
650 REPLACE INTO t1(c1,c2) VALUES(1,11);
651 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
652 SELECT * FROM t1 ORDER BY c1;
653 c1 c2
654 1 11
655 2 2
656 3 11
657 4 11
658 6 6
659 7 7
660 8 8
661 9 9
662 10 10
663 12 12
664 SELECT * FROM t2 ORDER BY c1;
665 c1 old1 new1 old2 new2
666 1 1 0 1 0
667 2 3 0 3 0
668 3 4 0 4 0
669 SELECT * FROM t3 ORDER BY c1;
670 c1 c2
671 1 8
672 DELETE FROM t1 WHERE c1=1;
673 DELETE FROM t1 WHERE c1=0;
674 DELETE FROM t1 WHERE c1=NULL;
675 DELETE FROM t1 WHERE c1=6 OR c1=7;
676 DELETE FROM t1 WHERE c2=3;
677 SELECT * FROM t1 ORDER BY c1;
678 c1 c2
679 2 2
680 3 11
681 4 11
682 8 8
683 9 9
684 10 10
685 12 12
686 SELECT * FROM t2 ORDER BY c1;
687 c1 old1 new1 old2 new2
688 1 1 0 1 0
689 2 3 0 3 0
690 3 4 0 4 0
691 4 1 0 11 0
692 5 6 0 6 0
693 6 7 0 7 0
694 SELECT * FROM t3 ORDER BY c1;
695 c1 c2
696 1 32
697 DROP TABLE t1,t2,t3;
698 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
699 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
700 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
701 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
702 INSERT INTO t3 VALUES(1,0);
703 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
704 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
705 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
706 END//
707 REPLACE INTO t1(c1,c2) VALUES(12,12);
708 REPLACE INTO t1(c1,c2) VALUES(1,11);
709 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
710 SELECT * FROM t1 ORDER BY c1;
711 c1 c2
712 1 11
713 2 2
714 3 11
715 4 11
716 6 6
717 7 7
718 8 8
719 9 9
720 10 10
721 12 12
722 SELECT * FROM t2 ORDER BY c1;
723 c1 old1 new1 old2 new2
724 1 1 0 1 0
725 2 3 0 3 0
726 3 4 0 4 0
727 SELECT * FROM t3 ORDER BY c1;
728 c1 c2
729 1 8
730 DELETE FROM t1 WHERE c1=1;
731 DELETE FROM t1 WHERE c1=0;
732 DELETE FROM t1 WHERE c1=NULL;
733 DELETE FROM t1 WHERE c1=6 OR c1=7;
734 DELETE FROM t1 WHERE c2=3;
735 SELECT * FROM t1 ORDER BY c1;
736 c1 c2
737 2 2
738 3 11
739 4 11
740 8 8
741 9 9
742 10 10
743 12 12
744 SELECT * FROM t2 ORDER BY c1;
745 c1 old1 new1 old2 new2
746 1 1 0 1 0
747 2 3 0 3 0
748 3 4 0 4 0
749 4 1 0 11 0
750 5 6 0 6 0
751 6 7 0 7 0
752 SELECT * FROM t3 ORDER BY c1;
753 c1 c2
754 1 32
755 DROP TABLE t1,t2,t3;
756 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
757 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
758 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
759 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
760 INSERT INTO t3 VALUES(1,0);
761 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
762 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
763 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
764 END//
765 REPLACE INTO t1(c1,c2) VALUES(12,12);
766 REPLACE INTO t1(c1,c2) VALUES(1,11);
767 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
768 SELECT * FROM t1 ORDER BY c1;
769 c1 c2
770 1 11
771 2 2
772 3 11
773 4 11
774 6 6
775 7 7
776 8 8
777 9 9
778 10 10
779 12 12
780 SELECT * FROM t2 ORDER BY c1;
781 c1 old1 new1 old2 new2
782 1 1 0 1 0
783 2 3 0 3 0
784 3 4 0 4 0
785 SELECT * FROM t3 ORDER BY c1;
786 c1 c2
787 1 8
788 DELETE FROM t1 WHERE c1=1;
789 DELETE FROM t1 WHERE c1=0;
790 DELETE FROM t1 WHERE c1=NULL;
791 DELETE FROM t1 WHERE c1=6 OR c1=7;
792 DELETE FROM t1 WHERE c2=3;
793 SELECT * FROM t1 ORDER BY c1;
794 c1 c2
795 2 2
796 3 11
797 4 11
798 8 8
799 9 9
800 10 10
801 12 12
802 SELECT * FROM t2 ORDER BY c1;
803 c1 old1 new1 old2 new2
804 1 1 0 1 0
805 2 3 0 3 0
806 3 4 0 4 0
807 4 1 0 11 0
808 5 6 0 6 0
809 6 7 0 7 0
810 SELECT * FROM t3 ORDER BY c1;
811 c1 c2
812 1 32
813 DROP TABLE t1,t2,t3;
814 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
815 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
816 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
817 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
818 INSERT INTO t3 VALUES(1,0);
819 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
820 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
821 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
822 END//
823 REPLACE INTO t1(c1,c2) VALUES(12,12);
824 REPLACE INTO t1(c1,c2) VALUES(1,11);
825 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
826 SELECT * FROM t1 ORDER BY c1;
827 c1 c2
828 1 11
829 2 2
830 3 11
831 4 11
832 6 6
833 7 7
834 8 8
835 9 9
836 10 10
837 12 12
838 SELECT * FROM t2 ORDER BY c1;
839 c1 old1 new1 old2 new2
840 1 1 0 1 0
841 2 3 0 3 0
842 3 4 0 4 0
843 SELECT * FROM t3 ORDER BY c1;
844 c1 c2
845 1 8
846 DELETE FROM t1 WHERE c1=1;
847 DELETE FROM t1 WHERE c1=0;
848 DELETE FROM t1 WHERE c1=NULL;
849 DELETE FROM t1 WHERE c1=6 OR c1=7;
850 DELETE FROM t1 WHERE c2=3;
851 SELECT * FROM t1 ORDER BY c1;
852 c1 c2
853 2 2
854 3 11
855 4 11
856 8 8
857 9 9
858 10 10
859 12 12
860 SELECT * FROM t2 ORDER BY c1;
861 c1 old1 new1 old2 new2
862 1 1 0 1 0
863 2 3 0 3 0
864 3 4 0 4 0
865 4 1 0 11 0
866 5 6 0 6 0
867 6 7 0 7 0
868 SELECT * FROM t3 ORDER BY c1;
869 c1 c2
870 1 32
871 DROP TABLE t1,t2,t3;
872 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
873 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
874 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
875 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
876 INSERT INTO t3 VALUES(1,0);
877 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
878 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
879 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
880 END//
881 REPLACE INTO t1(c1,c2) VALUES(12,12);
882 REPLACE INTO t1(c1,c2) VALUES(1,11);
883 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
884 SELECT * FROM t1 ORDER BY c1;
885 c1 c2
886 1 11
887 2 2
888 3 11
889 4 11
890 6 6
891 7 7
892 8 8
893 9 9
894 10 10
895 12 12
896 SELECT * FROM t2 ORDER BY c1;
897 c1 old1 new1 old2 new2
898 1 1 0 1 0
899 2 3 0 3 0
900 3 4 0 4 0
901 SELECT * FROM t3 ORDER BY c1;
902 c1 c2
903 1 8
904 DELETE FROM t1 WHERE c1=1;
905 DELETE FROM t1 WHERE c1=0;
906 DELETE FROM t1 WHERE c1=NULL;
907 DELETE FROM t1 WHERE c1=6 OR c1=7;
908 DELETE FROM t1 WHERE c2=3;
909 SELECT * FROM t1 ORDER BY c1;
910 c1 c2
911 2 2
912 3 11
913 4 11
914 8 8
915 9 9
916 10 10
917 12 12
918 SELECT * FROM t2 ORDER BY c1;
919 c1 old1 new1 old2 new2
920 1 1 0 1 0
921 2 3 0 3 0
922 3 4 0 4 0
923 4 1 0 11 0
924 5 6 0 6 0
925 6 7 0 7 0
926 SELECT * FROM t3 ORDER BY c1;
927 c1 c2
928 1 32
929 DROP TABLE t1,t2,t3;
930 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
931 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
932 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
933 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
934 INSERT INTO t3 VALUES(1,0);
935 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
936 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
937 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
938 END//
939 REPLACE INTO t1(c1,c2) VALUES(12,12);
940 REPLACE INTO t1(c1,c2) VALUES(1,11);
941 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
942 SELECT * FROM t1 ORDER BY c1;
943 c1 c2
944 1 11
945 2 2
946 3 11
947 4 11
948 6 6
949 7 7
950 8 8
951 9 9
952 10 10
953 12 12
954 SELECT * FROM t2 ORDER BY c1;
955 c1 old1 new1 old2 new2
956 1 1 0 1 0
957 2 3 0 3 0
958 3 4 0 4 0
959 SELECT * FROM t3 ORDER BY c1;
960 c1 c2
961 1 8
962 DELETE FROM t1 WHERE c1=1;
963 DELETE FROM t1 WHERE c1=0;
964 DELETE FROM t1 WHERE c1=NULL;
965 DELETE FROM t1 WHERE c1=6 OR c1=7;
966 DELETE FROM t1 WHERE c2=3;
967 SELECT * FROM t1 ORDER BY c1;
968 c1 c2
969 2 2
970 3 11
971 4 11
972 8 8
973 9 9
974 10 10
975 12 12
976 SELECT * FROM t2 ORDER BY c1;
977 c1 old1 new1 old2 new2
978 1 1 0 1 0
979 2 3 0 3 0
980 3 4 0 4 0
981 4 1 0 11 0
982 5 6 0 6 0
983 6 7 0 7 0
984 SELECT * FROM t3 ORDER BY c1;
985 c1 c2
986 1 32
987 DROP TABLE t1,t2,t3;
988 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
989 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
990 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
991 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
992 INSERT INTO t3 VALUES(1,0);
993 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
994 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
995 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
996 END//
997 REPLACE INTO t1(c1,c2) VALUES(12,12);
998 REPLACE INTO t1(c1,c2) VALUES(1,11);
999 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
1000 SELECT * FROM t1 ORDER BY c1;
1001 c1 c2
1002 1 11
1003 2 2
1004 3 11
1005 4 11
1006 6 6
1007 7 7
1008 8 8
1009 9 9
1010 10 10
1011 12 12
1012 SELECT * FROM t2 ORDER BY c1;
1013 c1 old1 new1 old2 new2
1014 1 1 0 1 0
1015 2 3 0 3 0
1016 3 4 0 4 0
1017 SELECT * FROM t3 ORDER BY c1;
1018 c1 c2
1019 1 8
1020 DELETE FROM t1 WHERE c1=1;
1021 DELETE FROM t1 WHERE c1=0;
1022 DELETE FROM t1 WHERE c1=NULL;
1023 DELETE FROM t1 WHERE c1=6 OR c1=7;
1024 DELETE FROM t1 WHERE c2=3;
1025 SELECT * FROM t1 ORDER BY c1;
1026 c1 c2
1027 2 2
1028 3 11
1029 4 11
1030 8 8
1031 9 9
1032 10 10
1033 12 12
1034 SELECT * FROM t2 ORDER BY c1;
1035 c1 old1 new1 old2 new2
1036 1 1 0 1 0
1037 2 3 0 3 0
1038 3 4 0 4 0
1039 4 1 0 11 0
1040 5 6 0 6 0
1041 6 7 0 7 0
1042 SELECT * FROM t3 ORDER BY c1;
1043 c1 c2
1044 1 32
1045 DROP TABLE t1,t2,t3;
1046 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
1047 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
1048 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
1049 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
1050 INSERT INTO t3 VALUES(1,0);
1051 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
1052 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
1053 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
1054 END//
1055 REPLACE INTO t1(c1,c2) VALUES(12,12);
1056 REPLACE INTO t1(c1,c2) VALUES(1,11);
1057 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
1058 SELECT * FROM t1 ORDER BY c1;
1059 c1 c2
1060 1 11
1061 2 2
1062 3 11
1063 4 11
1064 6 6
1065 7 7
1066 8 8
1067 9 9
1068 10 10
1069 12 12
1070 SELECT * FROM t2 ORDER BY c1;
1071 c1 old1 new1 old2 new2
1072 1 1 0 1 0
1073 2 3 0 3 0
1074 3 4 0 4 0
1075 SELECT * FROM t3 ORDER BY c1;
1076 c1 c2
1077 1 8
1078 DELETE FROM t1 WHERE c1=1;
1079 DELETE FROM t1 WHERE c1=0;
1080 DELETE FROM t1 WHERE c1=NULL;
1081 DELETE FROM t1 WHERE c1=6 OR c1=7;
1082 DELETE FROM t1 WHERE c2=3;
1083 SELECT * FROM t1 ORDER BY c1;
1084 c1 c2
1085 2 2
1086 3 11
1087 4 11
1088 8 8
1089 9 9
1090 10 10
1091 12 12
1092 SELECT * FROM t2 ORDER BY c1;
1093 c1 old1 new1 old2 new2
1094 1 1 0 1 0
1095 2 3 0 3 0
1096 3 4 0 4 0
1097 4 1 0 11 0
1098 5 6 0 6 0
1099 6 7 0 7 0
1100 SELECT * FROM t3 ORDER BY c1;
1101 c1 c2
1102 1 32
1103 DROP TABLE t1,t2,t3;
1104 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
1105 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
1106 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
1107 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
1108 INSERT INTO t3 VALUES(1,0);
1109 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
1110 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
1111 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
1112 END//
1113 REPLACE INTO t1(c1,c2) VALUES(12,12);
1114 REPLACE INTO t1(c1,c2) VALUES(1,11);
1115 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
1116 SELECT * FROM t1 ORDER BY c1;
1117 c1 c2
1118 1 11
1119 2 2
1120 3 11
1121 4 11
1122 6 6
1123 7 7
1124 8 8
1125 9 9
1126 10 10
1127 12 12
1128 SELECT * FROM t2 ORDER BY c1;
1129 c1 old1 new1 old2 new2
1130 1 1 0 1 0
1131 2 3 0 3 0
1132 3 4 0 4 0
1133 SELECT * FROM t3 ORDER BY c1;
1134 c1 c2
1135 1 8
1136 DELETE FROM t1 WHERE c1=1;
1137 DELETE FROM t1 WHERE c1=0;
1138 DELETE FROM t1 WHERE c1=NULL;
1139 DELETE FROM t1 WHERE c1=6 OR c1=7;
1140 DELETE FROM t1 WHERE c2=3;
1141 SELECT * FROM t1 ORDER BY c1;
1142 c1 c2
1143 2 2
1144 3 11
1145 4 11
1146 8 8
1147 9 9
1148 10 10
1149 12 12
1150 SELECT * FROM t2 ORDER BY c1;
1151 c1 old1 new1 old2 new2
1152 1 1 0 1 0
1153 2 3 0 3 0
1154 3 4 0 4 0
1155 4 1 0 11 0
1156 5 6 0 6 0
1157 6 7 0 7 0
1158 SELECT * FROM t3 ORDER BY c1;
1159 c1 c2
1160 1 32
1161 DROP TABLE t1,t2,t3;
1162 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
1163 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
1164 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
1165 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
1166 INSERT INTO t3 VALUES(1,0);
1167 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
1168 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
1169 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
1170 END//
1171 REPLACE INTO t1(c1,c2) VALUES(12,12);
1172 REPLACE INTO t1(c1,c2) VALUES(1,11);
1173 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
1174 SELECT * FROM t1 ORDER BY c1;
1175 c1 c2
1176 1 11
1177 2 2
1178 3 11
1179 4 11
1180 6 6
1181 7 7
1182 8 8
1183 9 9
1184 10 10
1185 12 12
1186 SELECT * FROM t2 ORDER BY c1;
1187 c1 old1 new1 old2 new2
1188 1 1 0 1 0
1189 2 3 0 3 0
1190 3 4 0 4 0
1191 SELECT * FROM t3 ORDER BY c1;
1192 c1 c2
1193 1 8
1194 DELETE FROM t1 WHERE c1=1;
1195 DELETE FROM t1 WHERE c1=0;
1196 DELETE FROM t1 WHERE c1=NULL;
1197 DELETE FROM t1 WHERE c1=6 OR c1=7;
1198 DELETE FROM t1 WHERE c2=3;
1199 SELECT * FROM t1 ORDER BY c1;
1200 c1 c2
1201 2 2
1202 3 11
1203 4 11
1204 8 8
1205 9 9
1206 10 10
1207 12 12
1208 SELECT * FROM t2 ORDER BY c1;
1209 c1 old1 new1 old2 new2
1210 1 1 0 1 0
1211 2 3 0 3 0
1212 3 4 0 4 0
1213 4 1 0 11 0
1214 5 6 0 6 0
1215 6 7 0 7 0
1216 SELECT * FROM t3 ORDER BY c1;
1217 c1 c2
1218 1 32
1219 DROP TABLE t1,t2,t3;
1220 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
1221 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
1222 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
1223 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
1224 INSERT INTO t3 VALUES(1,0);
1225 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
1226 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
1227 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
1228 END//
1229 REPLACE INTO t1(c1,c2) VALUES(12,12);
1230 REPLACE INTO t1(c1,c2) VALUES(1,11);
1231 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
1232 SELECT * FROM t1 ORDER BY c1;
1233 c1 c2
1234 1 11
1235 2 2
1236 3 11
1237 4 11
1238 6 6
1239 7 7
1240 8 8
1241 9 9
1242 10 10
1243 12 12
1244 SELECT * FROM t2 ORDER BY c1;
1245 c1 old1 new1 old2 new2
1246 1 1 0 1 0
1247 2 3 0 3 0
1248 3 4 0 4 0
1249 SELECT * FROM t3 ORDER BY c1;
1250 c1 c2
1251 1 8
1252 DELETE FROM t1 WHERE c1=1;
1253 DELETE FROM t1 WHERE c1=0;
1254 DELETE FROM t1 WHERE c1=NULL;
1255 DELETE FROM t1 WHERE c1=6 OR c1=7;
1256 DELETE FROM t1 WHERE c2=3;
1257 SELECT * FROM t1 ORDER BY c1;
1258 c1 c2
1259 2 2
1260 3 11
1261 4 11
1262 8 8
1263 9 9
1264 10 10
1265 12 12
1266 SELECT * FROM t2 ORDER BY c1;
1267 c1 old1 new1 old2 new2
1268 1 1 0 1 0
1269 2 3 0 3 0
1270 3 4 0 4 0
1271 4 1 0 11 0
1272 5 6 0 6 0
1273 6 7 0 7 0
1274 SELECT * FROM t3 ORDER BY c1;
1275 c1 c2
1276 1 32
1277 DROP TABLE t1,t2,t3;