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;
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;
27 SELECT * FROM t2 ORDER BY c1;
28 c1 old1 new1 old2 new2
29 SELECT * FROM t3 ORDER BY c1;
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;
46 SELECT * FROM t2 ORDER BY c1;
47 c1 old1 new1 old2 new2
48 SELECT * FROM t3 ORDER BY c1;
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;
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;
77 SELECT * FROM t2 ORDER BY c1;
78 c1 old1 new1 old2 new2
79 SELECT * FROM t3 ORDER BY c1;
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;
96 SELECT * FROM t2 ORDER BY c1;
97 c1 old1 new1 old2 new2
98 SELECT * FROM t3 ORDER BY c1;
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;
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;
127 SELECT * FROM t2 ORDER BY c1;
128 c1 old1 new1 old2 new2
129 SELECT * FROM t3 ORDER BY c1;
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;
146 SELECT * FROM t2 ORDER BY c1;
147 c1 old1 new1 old2 new2
148 SELECT * FROM t3 ORDER BY c1;
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;
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;
177 SELECT * FROM t2 ORDER BY c1;
178 c1 old1 new1 old2 new2
179 SELECT * FROM t3 ORDER BY c1;
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;
196 SELECT * FROM t2 ORDER BY c1;
197 c1 old1 new1 old2 new2
198 SELECT * FROM t3 ORDER BY c1;
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;
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;
227 SELECT * FROM t2 ORDER BY c1;
228 c1 old1 new1 old2 new2
229 SELECT * FROM t3 ORDER BY c1;
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;
246 SELECT * FROM t2 ORDER BY c1;
247 c1 old1 new1 old2 new2
248 SELECT * FROM t3 ORDER BY c1;
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;
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;
277 SELECT * FROM t2 ORDER BY c1;
278 c1 old1 new1 old2 new2
279 SELECT * FROM t3 ORDER BY c1;
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;
296 SELECT * FROM t2 ORDER BY c1;
297 c1 old1 new1 old2 new2
298 SELECT * FROM t3 ORDER BY c1;
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;
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;
327 SELECT * FROM t2 ORDER BY c1;
328 c1 old1 new1 old2 new2
329 SELECT * FROM t3 ORDER BY c1;
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;
346 SELECT * FROM t2 ORDER BY c1;
347 c1 old1 new1 old2 new2
348 SELECT * FROM t3 ORDER BY c1;
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;
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;
377 SELECT * FROM t2 ORDER BY c1;
378 c1 old1 new1 old2 new2
379 SELECT * FROM t3 ORDER BY c1;
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;
396 SELECT * FROM t2 ORDER BY c1;
397 c1 old1 new1 old2 new2
398 SELECT * FROM t3 ORDER BY c1;
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;
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;
427 SELECT * FROM t2 ORDER BY c1;
428 c1 old1 new1 old2 new2
429 SELECT * FROM t3 ORDER BY c1;
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;
446 SELECT * FROM t2 ORDER BY c1;
447 c1 old1 new1 old2 new2
448 SELECT * FROM t3 ORDER BY c1;
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;
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;
477 SELECT * FROM t2 ORDER BY c1;
478 c1 old1 new1 old2 new2
479 SELECT * FROM t3 ORDER BY c1;
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;
496 SELECT * FROM t2 ORDER BY c1;
497 c1 old1 new1 old2 new2
498 SELECT * FROM t3 ORDER BY c1;
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;
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;
527 SELECT * FROM t2 ORDER BY c1;
528 c1 old1 new1 old2 new2
529 SELECT * FROM t3 ORDER BY c1;
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;
546 SELECT * FROM t2 ORDER BY c1;
547 c1 old1 new1 old2 new2
548 SELECT * FROM t3 ORDER BY c1;
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;
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;
577 SELECT * FROM t2 ORDER BY c1;
578 c1 old1 new1 old2 new2
579 SELECT * FROM t3 ORDER BY c1;
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;
596 SELECT * FROM t2 ORDER BY c1;
597 c1 old1 new1 old2 new2
598 SELECT * FROM t3 ORDER BY c1;
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;
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;
627 SELECT * FROM t2 ORDER BY c1;
628 c1 old1 new1 old2 new2
629 SELECT * FROM t3 ORDER BY c1;
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;
646 SELECT * FROM t2 ORDER BY c1;
647 c1 old1 new1 old2 new2
648 SELECT * FROM t3 ORDER BY c1;
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;
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;
677 SELECT * FROM t2 ORDER BY c1;
678 c1 old1 new1 old2 new2
679 SELECT * FROM t3 ORDER BY c1;
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;
696 SELECT * FROM t2 ORDER BY c1;
697 c1 old1 new1 old2 new2
698 SELECT * FROM t3 ORDER BY c1;
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;
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;
727 SELECT * FROM t2 ORDER BY c1;
728 c1 old1 new1 old2 new2
729 SELECT * FROM t3 ORDER BY c1;
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;
746 SELECT * FROM t2 ORDER BY c1;
747 c1 old1 new1 old2 new2
748 SELECT * FROM t3 ORDER BY c1;
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;
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;
777 SELECT * FROM t2 ORDER BY c1;
778 c1 old1 new1 old2 new2
779 SELECT * FROM t3 ORDER BY c1;
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;
796 SELECT * FROM t2 ORDER BY c1;
797 c1 old1 new1 old2 new2
798 SELECT * FROM t3 ORDER BY c1;
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;
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;
827 SELECT * FROM t2 ORDER BY c1;
828 c1 old1 new1 old2 new2
829 SELECT * FROM t3 ORDER BY c1;
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;
846 SELECT * FROM t2 ORDER BY c1;
847 c1 old1 new1 old2 new2
848 SELECT * FROM t3 ORDER BY c1;
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;
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;
877 SELECT * FROM t2 ORDER BY c1;
878 c1 old1 new1 old2 new2
879 SELECT * FROM t3 ORDER BY c1;
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;
896 SELECT * FROM t2 ORDER BY c1;
897 c1 old1 new1 old2 new2
898 SELECT * FROM t3 ORDER BY c1;
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;
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;
927 SELECT * FROM t2 ORDER BY c1;
928 c1 old1 new1 old2 new2
929 SELECT * FROM t3 ORDER BY c1;
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;
946 SELECT * FROM t2 ORDER BY c1;
947 c1 old1 new1 old2 new2
948 SELECT * FROM t3 ORDER BY c1;
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;
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;
977 SELECT * FROM t2 ORDER BY c1;
978 c1 old1 new1 old2 new2
979 SELECT * FROM t3 ORDER BY c1;
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;
996 SELECT * FROM t2 ORDER BY c1;
997 c1 old1 new1 old2 new2
998 SELECT * FROM t3 ORDER BY c1;
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;
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;
1027 SELECT * FROM t2 ORDER BY c1;
1028 c1 old1 new1 old2 new2
1029 SELECT * FROM t3 ORDER BY c1;
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;
1046 SELECT * FROM t2 ORDER BY c1;
1047 c1 old1 new1 old2 new2
1048 SELECT * FROM t3 ORDER BY c1;
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;
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;
1077 SELECT * FROM t2 ORDER BY c1;
1078 c1 old1 new1 old2 new2
1079 SELECT * FROM t3 ORDER BY c1;
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;
1096 SELECT * FROM t2 ORDER BY c1;
1097 c1 old1 new1 old2 new2
1098 SELECT * FROM t3 ORDER BY c1;
1101 DROP TABLE t1,t2,t3;