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