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