Debian Codegraph @

mysql-test/suite/engines/funcs/t/tr_insert.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3;
3 --enable_warnings
4 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
5 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
6 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
7 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
8 INSERT INTO t3 VALUES(1,0);
9 delimiter //;
10 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
11 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
12 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
13 END//
14 DELIMITER ;//
15 INSERT INTO t1(c1,c2) VALUES(11,11);
16 REPLACE INTO t1(c1,c2) VALUES(12,12);
17 REPLACE INTO t1(c1,c2) VALUES(1,11);
18 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
19 SELECT * FROM t1 ORDER BY c1;
20 SELECT * FROM t2 ORDER BY c1;
21 SELECT * FROM t3 ORDER BY c1;
22 DROP TABLE t1,t2,t3;
23 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
24 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
25 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
26 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
27 INSERT INTO t3 VALUES(1,0);
28 delimiter //;
29 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
30 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
31 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
32 END//
33 DELIMITER ;//
34 INSERT INTO t1(c1,c2) VALUES(11,11);
35 REPLACE INTO t1(c1,c2) VALUES(12,12);
36 REPLACE INTO t1(c1,c2) VALUES(1,11);
37 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
38 SELECT * FROM t1 ORDER BY c1;
39 SELECT * FROM t2 ORDER BY c1;
40 SELECT * FROM t3 ORDER BY c1;
41 DROP TABLE t1,t2,t3;
42 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
43 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
44 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
45 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
46 INSERT INTO t3 VALUES(1,0);
47 delimiter //;
48 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
49 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
50 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
51 END//
52 DELIMITER ;//
53 INSERT INTO t1(c1,c2) VALUES(11,11);
54 REPLACE INTO t1(c1,c2) VALUES(12,12);
55 REPLACE INTO t1(c1,c2) VALUES(1,11);
56 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
57 SELECT * FROM t1 ORDER BY c1;
58 SELECT * FROM t2 ORDER BY c1;
59 SELECT * FROM t3 ORDER BY c1;
60 DROP TABLE t1,t2,t3;
61 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
62 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
63 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
64 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
65 INSERT INTO t3 VALUES(1,0);
66 delimiter //;
67 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
68 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
69 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
70 END//
71 DELIMITER ;//
72 INSERT INTO t1(c1,c2) VALUES(11,11);
73 REPLACE INTO t1(c1,c2) VALUES(12,12);
74 REPLACE INTO t1(c1,c2) VALUES(1,11);
75 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
76 SELECT * FROM t1 ORDER BY c1;
77 SELECT * FROM t2 ORDER BY c1;
78 SELECT * FROM t3 ORDER BY c1;
79 DROP TABLE t1,t2,t3;
80 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
81 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
82 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
83 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
84 INSERT INTO t3 VALUES(1,0);
85 delimiter //;
86 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
87 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
88 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
89 END//
90 DELIMITER ;//
91 INSERT INTO t1(c1,c2) VALUES(11,11);
92 REPLACE INTO t1(c1,c2) VALUES(12,12);
93 REPLACE INTO t1(c1,c2) VALUES(1,11);
94 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
95 SELECT * FROM t1 ORDER BY c1;
96 SELECT * FROM t2 ORDER BY c1;
97 SELECT * FROM t3 ORDER BY c1;
98 DROP TABLE t1,t2,t3;
99 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
100 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
101 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
102 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
103 INSERT INTO t3 VALUES(1,0);
104 delimiter //;
105 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
106 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
107 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
108 END//
109 DELIMITER ;//
110 INSERT INTO t1(c1,c2) VALUES(11,11);
111 REPLACE INTO t1(c1,c2) VALUES(12,12);
112 REPLACE INTO t1(c1,c2) VALUES(1,11);
113 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
114 SELECT * FROM t1 ORDER BY c1;
115 SELECT * FROM t2 ORDER BY c1;
116 SELECT * FROM t3 ORDER BY c1;
117 DROP TABLE t1,t2,t3;
118 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
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 REAL, new1 REAL, old2 REAL, new2 REAL);
121 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
122 INSERT INTO t3 VALUES(1,0);
123 delimiter //;
124 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
125 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
126 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
127 END//
128 DELIMITER ;//
129 INSERT INTO t1(c1,c2) VALUES(11,11);
130 REPLACE INTO t1(c1,c2) VALUES(12,12);
131 REPLACE INTO t1(c1,c2) VALUES(1,11);
132 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
133 SELECT * FROM t1 ORDER BY c1;
134 SELECT * FROM t2 ORDER BY c1;
135 SELECT * FROM t3 ORDER BY c1;
136 DROP TABLE t1,t2,t3;
137 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
138 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
139 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
140 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
141 INSERT INTO t3 VALUES(1,0);
142 delimiter //;
143 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
144 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
145 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
146 END//
147 DELIMITER ;//
148 INSERT INTO t1(c1,c2) VALUES(11,11);
149 REPLACE INTO t1(c1,c2) VALUES(12,12);
150 REPLACE INTO t1(c1,c2) VALUES(1,11);
151 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
152 SELECT * FROM t1 ORDER BY c1;
153 SELECT * FROM t2 ORDER BY c1;
154 SELECT * FROM t3 ORDER BY c1;
155 DROP TABLE t1,t2,t3;
156 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
157 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
158 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
159 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
160 INSERT INTO t3 VALUES(1,0);
161 delimiter //;
162 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
163 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
164 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
165 END//
166 DELIMITER ;//
167 INSERT INTO t1(c1,c2) VALUES(11,11);
168 REPLACE INTO t1(c1,c2) VALUES(12,12);
169 REPLACE INTO t1(c1,c2) VALUES(1,11);
170 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
171 SELECT * FROM t1 ORDER BY c1;
172 SELECT * FROM t2 ORDER BY c1;
173 SELECT * FROM t3 ORDER BY c1;
174 DROP TABLE t1,t2,t3;
175 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
176 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
177 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
178 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
179 INSERT INTO t3 VALUES(1,0);
180 delimiter //;
181 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
182 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
183 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
184 END//
185 DELIMITER ;//
186 INSERT INTO t1(c1,c2) VALUES(11,11);
187 REPLACE INTO t1(c1,c2) VALUES(12,12);
188 REPLACE INTO t1(c1,c2) VALUES(1,11);
189 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
190 SELECT * FROM t1 ORDER BY c1;
191 SELECT * FROM t2 ORDER BY c1;
192 SELECT * FROM t3 ORDER BY c1;
193 DROP TABLE t1,t2,t3;
194 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
195 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
196 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
197 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
198 INSERT INTO t3 VALUES(1,0);
199 delimiter //;
200 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
201 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
202 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
203 END//
204 DELIMITER ;//
205 INSERT INTO t1(c1,c2) VALUES(11,11);
206 REPLACE INTO t1(c1,c2) VALUES(12,12);
207 REPLACE INTO t1(c1,c2) VALUES(1,11);
208 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
209 SELECT * FROM t1 ORDER BY c1;
210 SELECT * FROM t2 ORDER BY c1;
211 SELECT * FROM t3 ORDER BY c1;
212 DROP TABLE t1,t2,t3;
213 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
214 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
215 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
216 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
217 INSERT INTO t3 VALUES(1,0);
218 delimiter //;
219 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
220 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
221 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
222 END//
223 DELIMITER ;//
224 INSERT INTO t1(c1,c2) VALUES(11,11);
225 REPLACE INTO t1(c1,c2) VALUES(12,12);
226 REPLACE INTO t1(c1,c2) VALUES(1,11);
227 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
228 SELECT * FROM t1 ORDER BY c1;
229 SELECT * FROM t2 ORDER BY c1;
230 SELECT * FROM t3 ORDER BY c1;
231 DROP TABLE t1,t2,t3;
232 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
233 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
234 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
235 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
236 INSERT INTO t3 VALUES(1,0);
237 delimiter //;
238 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
239 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
240 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
241 END//
242 DELIMITER ;//
243 INSERT INTO t1(c1,c2) VALUES(11,11);
244 REPLACE INTO t1(c1,c2) VALUES(12,12);
245 REPLACE INTO t1(c1,c2) VALUES(1,11);
246 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
247 SELECT * FROM t1 ORDER BY c1;
248 SELECT * FROM t2 ORDER BY c1;
249 SELECT * FROM t3 ORDER BY c1;
250 DROP TABLE t1,t2,t3;
251 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
252 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
253 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
254 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
255 INSERT INTO t3 VALUES(1,0);
256 delimiter //;
257 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
258 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
259 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
260 END//
261 DELIMITER ;//
262 INSERT INTO t1(c1,c2) VALUES(11,11);
263 REPLACE INTO t1(c1,c2) VALUES(12,12);
264 REPLACE INTO t1(c1,c2) VALUES(1,11);
265 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
266 SELECT * FROM t1 ORDER BY c1;
267 SELECT * FROM t2 ORDER BY c1;
268 SELECT * FROM t3 ORDER BY c1;
269 DROP TABLE t1,t2,t3;
270 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
271 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
272 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
273 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
274 INSERT INTO t3 VALUES(1,0);
275 delimiter //;
276 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
277 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
278 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
279 END//
280 DELIMITER ;//
281 INSERT INTO t1(c1,c2) VALUES(11,11);
282 REPLACE INTO t1(c1,c2) VALUES(12,12);
283 REPLACE INTO t1(c1,c2) VALUES(1,11);
284 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
285 SELECT * FROM t1 ORDER BY c1;
286 SELECT * FROM t2 ORDER BY c1;
287 SELECT * FROM t3 ORDER BY c1;
288 DROP TABLE t1,t2,t3;
289 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
290 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
291 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
292 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
293 INSERT INTO t3 VALUES(1,0);
294 delimiter //;
295 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
296 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
297 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
298 END//
299 DELIMITER ;//
300 INSERT INTO t1(c1,c2) VALUES(11,11);
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 SELECT * FROM t2 ORDER BY c1;
306 SELECT * FROM t3 ORDER BY c1;
307 DROP TABLE t1,t2,t3;
308 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
309 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
310 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
311 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
312 INSERT INTO t3 VALUES(1,0);
313 delimiter //;
314 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
315 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
316 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
317 END//
318 DELIMITER ;//
319 INSERT INTO t1(c1,c2) VALUES(11,11);
320 REPLACE INTO t1(c1,c2) VALUES(12,12);
321 REPLACE INTO t1(c1,c2) VALUES(1,11);
322 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
323 SELECT * FROM t1 ORDER BY c1;
324 SELECT * FROM t2 ORDER BY c1;
325 SELECT * FROM t3 ORDER BY c1;
326 DROP TABLE t1,t2,t3;
327 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
328 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
329 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
330 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
331 INSERT INTO t3 VALUES(1,0);
332 delimiter //;
333 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
334 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
335 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
336 END//
337 DELIMITER ;//
338 INSERT INTO t1(c1,c2) VALUES(11,11);
339 REPLACE INTO t1(c1,c2) VALUES(12,12);
340 REPLACE INTO t1(c1,c2) VALUES(1,11);
341 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
342 SELECT * FROM t1 ORDER BY c1;
343 SELECT * FROM t2 ORDER BY c1;
344 SELECT * FROM t3 ORDER BY c1;
345 DROP TABLE t1,t2,t3;
346 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
347 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
348 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
349 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
350 INSERT INTO t3 VALUES(1,0);
351 delimiter //;
352 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
353 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
354 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
355 END//
356 DELIMITER ;//
357 INSERT INTO t1(c1,c2) VALUES(11,11);
358 REPLACE INTO t1(c1,c2) VALUES(12,12);
359 REPLACE INTO t1(c1,c2) VALUES(1,11);
360 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
361 SELECT * FROM t1 ORDER BY c1;
362 SELECT * FROM t2 ORDER BY c1;
363 SELECT * FROM t3 ORDER BY c1;
364 DROP TABLE t1,t2,t3;
365 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
366 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
367 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
368 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
369 INSERT INTO t3 VALUES(1,0);
370 delimiter //;
371 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
372 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
373 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
374 END//
375 DELIMITER ;//
376 INSERT INTO t1(c1,c2) VALUES(11,11);
377 REPLACE INTO t1(c1,c2) VALUES(12,12);
378 REPLACE INTO t1(c1,c2) VALUES(1,11);
379 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
380 SELECT * FROM t1 ORDER BY c1;
381 SELECT * FROM t2 ORDER BY c1;
382 SELECT * FROM t3 ORDER BY c1;
383 DROP TABLE t1,t2,t3;
384 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
385 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
386 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
387 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
388 INSERT INTO t3 VALUES(1,0);
389 delimiter //;
390 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
391 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
392 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
393 END//
394 DELIMITER ;//
395 INSERT INTO t1(c1,c2) VALUES(11,11);
396 REPLACE INTO t1(c1,c2) VALUES(12,12);
397 REPLACE INTO t1(c1,c2) VALUES(1,11);
398 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
399 SELECT * FROM t1 ORDER BY c1;
400 SELECT * FROM t2 ORDER BY c1;
401 SELECT * FROM t3 ORDER BY c1;
402 DROP TABLE t1,t2,t3;
403 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
404 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
405 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
406 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
407 INSERT INTO t3 VALUES(1,0);
408 delimiter //;
409 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
410 INSERT INTO t2 (old1,new1,old2,new2) VALUES(0,NEW.c1,0,NEW.c2);
411 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
412 END//
413 DELIMITER ;//
414 INSERT INTO t1(c1,c2) VALUES(11,11);
415 REPLACE INTO t1(c1,c2) VALUES(12,12);
416 REPLACE INTO t1(c1,c2) VALUES(1,11);
417 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
418 SELECT * FROM t1 ORDER BY c1;
419 SELECT * FROM t2 ORDER BY c1;
420 SELECT * FROM t3 ORDER BY c1;
421 DROP TABLE t1,t2,t3;
422