Debian Codegraph @

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