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;