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 DELETE ON t1 FOR EACH ROW BEGIN 11INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 12UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 13END// 14DELIMITER ;// 15REPLACE INTO t1(c1,c2) VALUES(12,12); 16REPLACE INTO t1(c1,c2) VALUES(1,11); 17REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 18SELECT * FROM t1 ORDER BY c1; 19SELECT * FROM t2 ORDER BY c1; 20SELECT * FROM t3 ORDER BY c1; 21DELETE FROM t1 WHERE c1=1; 22DELETE FROM t1 WHERE c1=0; 23DELETE FROM t1 WHERE c1=NULL; 24DELETE FROM t1 WHERE c1=6 OR c1=7; 25DELETE FROM t1 WHERE c2=3; 26SELECT * FROM t1 ORDER BY c1; 27SELECT * FROM t2 ORDER BY c1; 28SELECT * FROM t3 ORDER BY c1; 29DROP TABLE t1,t2,t3; 30CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT); 31INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 32CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT); 33CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT); 34INSERT INTO t3 VALUES(1,0); 35delimiter //; 36CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 37INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 38UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 39END// 40DELIMITER ;// 41REPLACE INTO t1(c1,c2) VALUES(12,12); 42REPLACE INTO t1(c1,c2) VALUES(1,11); 43REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 44SELECT * FROM t1 ORDER BY c1; 45SELECT * FROM t2 ORDER BY c1; 46SELECT * FROM t3 ORDER BY c1; 47DELETE FROM t1 WHERE c1=1; 48DELETE FROM t1 WHERE c1=0; 49DELETE FROM t1 WHERE c1=NULL; 50DELETE FROM t1 WHERE c1=6 OR c1=7; 51DELETE FROM t1 WHERE c2=3; 52SELECT * FROM t1 ORDER BY c1; 53SELECT * FROM t2 ORDER BY c1; 54SELECT * FROM t3 ORDER BY c1; 55DROP TABLE t1,t2,t3; 56CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT); 57INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 58CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT); 59CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT); 60INSERT INTO t3 VALUES(1,0); 61delimiter //; 62CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 63INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 64UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 65END// 66DELIMITER ;// 67REPLACE INTO t1(c1,c2) VALUES(12,12); 68REPLACE INTO t1(c1,c2) VALUES(1,11); 69REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 70SELECT * FROM t1 ORDER BY c1; 71SELECT * FROM t2 ORDER BY c1; 72SELECT * FROM t3 ORDER BY c1; 73DELETE FROM t1 WHERE c1=1; 74DELETE FROM t1 WHERE c1=0; 75DELETE FROM t1 WHERE c1=NULL; 76DELETE FROM t1 WHERE c1=6 OR c1=7; 77DELETE FROM t1 WHERE c2=3; 78SELECT * FROM t1 ORDER BY c1; 79SELECT * FROM t2 ORDER BY c1; 80SELECT * FROM t3 ORDER BY c1; 81DROP TABLE t1,t2,t3; 82CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT); 83INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 84CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT); 85CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT); 86INSERT INTO t3 VALUES(1,0); 87delimiter //; 88CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 89INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 90UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 91END// 92DELIMITER ;// 93REPLACE INTO t1(c1,c2) VALUES(12,12); 94REPLACE INTO t1(c1,c2) VALUES(1,11); 95REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 96SELECT * FROM t1 ORDER BY c1; 97SELECT * FROM t2 ORDER BY c1; 98SELECT * FROM t3 ORDER BY c1; 99DELETE FROM t1 WHERE c1=1; 100DELETE FROM t1 WHERE c1=0; 101DELETE FROM t1 WHERE c1=NULL; 102DELETE FROM t1 WHERE c1=6 OR c1=7; 103DELETE FROM t1 WHERE c2=3; 104SELECT * FROM t1 ORDER BY c1; 105SELECT * FROM t2 ORDER BY c1; 106SELECT * FROM t3 ORDER BY c1; 107DROP TABLE t1,t2,t3; 108CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER); 109INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 110CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER); 111CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER); 112INSERT INTO t3 VALUES(1,0); 113delimiter //; 114CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 115INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 116UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 117END// 118DELIMITER ;// 119REPLACE INTO t1(c1,c2) VALUES(12,12); 120REPLACE INTO t1(c1,c2) VALUES(1,11); 121REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 122SELECT * FROM t1 ORDER BY c1; 123SELECT * FROM t2 ORDER BY c1; 124SELECT * FROM t3 ORDER BY c1; 125DELETE FROM t1 WHERE c1=1; 126DELETE FROM t1 WHERE c1=0; 127DELETE FROM t1 WHERE c1=NULL; 128DELETE FROM t1 WHERE c1=6 OR c1=7; 129DELETE FROM t1 WHERE c2=3; 130SELECT * FROM t1 ORDER BY c1; 131SELECT * FROM t2 ORDER BY c1; 132SELECT * FROM t3 ORDER BY c1; 133DROP TABLE t1,t2,t3; 134CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT); 135INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 136CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT); 137CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT); 138INSERT INTO t3 VALUES(1,0); 139delimiter //; 140CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 141INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 142UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 143END// 144DELIMITER ;// 145REPLACE INTO t1(c1,c2) VALUES(12,12); 146REPLACE INTO t1(c1,c2) VALUES(1,11); 147REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 148SELECT * FROM t1 ORDER BY c1; 149SELECT * FROM t2 ORDER BY c1; 150SELECT * FROM t3 ORDER BY c1; 151DELETE FROM t1 WHERE c1=1; 152DELETE FROM t1 WHERE c1=0; 153DELETE FROM t1 WHERE c1=NULL; 154DELETE FROM t1 WHERE c1=6 OR c1=7; 155DELETE FROM t1 WHERE c2=3; 156SELECT * FROM t1 ORDER BY c1; 157SELECT * FROM t2 ORDER BY c1; 158SELECT * FROM t3 ORDER BY c1; 159DROP TABLE t1,t2,t3; 160CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL); 161INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 162CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL); 163CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL); 164INSERT INTO t3 VALUES(1,0); 165delimiter //; 166CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 167INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 168UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 169END// 170DELIMITER ;// 171REPLACE INTO t1(c1,c2) VALUES(12,12); 172REPLACE INTO t1(c1,c2) VALUES(1,11); 173REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 174SELECT * FROM t1 ORDER BY c1; 175SELECT * FROM t2 ORDER BY c1; 176SELECT * FROM t3 ORDER BY c1; 177DELETE FROM t1 WHERE c1=1; 178DELETE FROM t1 WHERE c1=0; 179DELETE FROM t1 WHERE c1=NULL; 180DELETE FROM t1 WHERE c1=6 OR c1=7; 181DELETE FROM t1 WHERE c2=3; 182SELECT * FROM t1 ORDER BY c1; 183SELECT * FROM t2 ORDER BY c1; 184SELECT * FROM t3 ORDER BY c1; 185DROP TABLE t1,t2,t3; 186CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE); 187INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 188CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE); 189CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE); 190INSERT INTO t3 VALUES(1,0); 191delimiter //; 192CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 193INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 194UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 195END// 196DELIMITER ;// 197REPLACE INTO t1(c1,c2) VALUES(12,12); 198REPLACE INTO t1(c1,c2) VALUES(1,11); 199REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 200SELECT * FROM t1 ORDER BY c1; 201SELECT * FROM t2 ORDER BY c1; 202SELECT * FROM t3 ORDER BY c1; 203DELETE FROM t1 WHERE c1=1; 204DELETE FROM t1 WHERE c1=0; 205DELETE FROM t1 WHERE c1=NULL; 206DELETE FROM t1 WHERE c1=6 OR c1=7; 207DELETE FROM t1 WHERE c2=3; 208SELECT * FROM t1 ORDER BY c1; 209SELECT * FROM t2 ORDER BY c1; 210SELECT * FROM t3 ORDER BY c1; 211DROP TABLE t1,t2,t3; 212CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT); 213INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 214CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT); 215CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT); 216INSERT INTO t3 VALUES(1,0); 217delimiter //; 218CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 219INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 220UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 221END// 222DELIMITER ;// 223REPLACE INTO t1(c1,c2) VALUES(12,12); 224REPLACE INTO t1(c1,c2) VALUES(1,11); 225REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 226SELECT * FROM t1 ORDER BY c1; 227SELECT * FROM t2 ORDER BY c1; 228SELECT * FROM t3 ORDER BY c1; 229DELETE FROM t1 WHERE c1=1; 230DELETE FROM t1 WHERE c1=0; 231DELETE FROM t1 WHERE c1=NULL; 232DELETE FROM t1 WHERE c1=6 OR c1=7; 233DELETE FROM t1 WHERE c2=3; 234SELECT * FROM t1 ORDER BY c1; 235SELECT * FROM t2 ORDER BY c1; 236SELECT * FROM t3 ORDER BY c1; 237DROP TABLE t1,t2,t3; 238CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL); 239INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 240CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL); 241CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL); 242INSERT INTO t3 VALUES(1,0); 243delimiter //; 244CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 245INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 246UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 247END// 248DELIMITER ;// 249REPLACE INTO t1(c1,c2) VALUES(12,12); 250REPLACE INTO t1(c1,c2) VALUES(1,11); 251REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 252SELECT * FROM t1 ORDER BY c1; 253SELECT * FROM t2 ORDER BY c1; 254SELECT * FROM t3 ORDER BY c1; 255DELETE FROM t1 WHERE c1=1; 256DELETE FROM t1 WHERE c1=0; 257DELETE FROM t1 WHERE c1=NULL; 258DELETE FROM t1 WHERE c1=6 OR c1=7; 259DELETE FROM t1 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 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC); 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 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC); 267CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC); 268INSERT INTO t3 VALUES(1,0); 269delimiter //; 270CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN 271INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 272UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 273END// 274DELIMITER ;// 275REPLACE INTO t1(c1,c2) VALUES(12,12); 276REPLACE INTO t1(c1,c2) VALUES(1,11); 277REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 278SELECT * FROM t1 ORDER BY c1; 279SELECT * FROM t2 ORDER BY c1; 280SELECT * FROM t3 ORDER BY c1; 281DELETE FROM t1 WHERE c1=1; 282DELETE FROM t1 WHERE c1=0; 283DELETE FROM t1 WHERE c1=NULL; 284DELETE FROM t1 WHERE c1=6 OR c1=7; 285DELETE FROM t1 WHERE c2=3; 286SELECT * FROM t1 ORDER BY c1; 287SELECT * FROM t2 ORDER BY c1; 288SELECT * FROM t3 ORDER BY c1; 289DROP TABLE t1,t2,t3; 290CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT); 291INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 292CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT); 293CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT); 294INSERT INTO t3 VALUES(1,0); 295delimiter //; 296CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 297INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 298UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 299END// 300DELIMITER ;// 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; 307DELETE FROM t1 WHERE c1=1; 308DELETE FROM t1 WHERE c1=0; 309DELETE FROM t1 WHERE c1=NULL; 310DELETE FROM t1 WHERE c1=6 OR c1=7; 311DELETE FROM t1 WHERE c2=3; 312SELECT * FROM t1 ORDER BY c1; 313SELECT * FROM t2 ORDER BY c1; 314SELECT * FROM t3 ORDER BY c1; 315DROP TABLE t1,t2,t3; 316CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT); 317INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 318CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT); 319CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT); 320INSERT INTO t3 VALUES(1,0); 321delimiter //; 322CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 323INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 324UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 325END// 326DELIMITER ;// 327REPLACE INTO t1(c1,c2) VALUES(12,12); 328REPLACE INTO t1(c1,c2) VALUES(1,11); 329REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 330SELECT * FROM t1 ORDER BY c1; 331SELECT * FROM t2 ORDER BY c1; 332SELECT * FROM t3 ORDER BY c1; 333DELETE FROM t1 WHERE c1=1; 334DELETE FROM t1 WHERE c1=0; 335DELETE FROM t1 WHERE c1=NULL; 336DELETE FROM t1 WHERE c1=6 OR c1=7; 337DELETE FROM t1 WHERE c2=3; 338SELECT * FROM t1 ORDER BY c1; 339SELECT * FROM t2 ORDER BY c1; 340SELECT * FROM t3 ORDER BY c1; 341DROP TABLE t1,t2,t3; 342CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT); 343INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 344CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT); 345CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT); 346INSERT INTO t3 VALUES(1,0); 347delimiter //; 348CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 349INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 350UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 351END// 352DELIMITER ;// 353REPLACE INTO t1(c1,c2) VALUES(12,12); 354REPLACE INTO t1(c1,c2) VALUES(1,11); 355REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 356SELECT * FROM t1 ORDER BY c1; 357SELECT * FROM t2 ORDER BY c1; 358SELECT * FROM t3 ORDER BY c1; 359DELETE FROM t1 WHERE c1=1; 360DELETE FROM t1 WHERE c1=0; 361DELETE FROM t1 WHERE c1=NULL; 362DELETE FROM t1 WHERE c1=6 OR c1=7; 363DELETE FROM t1 WHERE c2=3; 364SELECT * FROM t1 ORDER BY c1; 365SELECT * FROM t2 ORDER BY c1; 366SELECT * FROM t3 ORDER BY c1; 367DROP TABLE t1,t2,t3; 368CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT); 369INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 370CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT); 371CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT); 372INSERT INTO t3 VALUES(1,0); 373delimiter //; 374CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 375INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 376UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 377END// 378DELIMITER ;// 379REPLACE INTO t1(c1,c2) VALUES(12,12); 380REPLACE INTO t1(c1,c2) VALUES(1,11); 381REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 382SELECT * FROM t1 ORDER BY c1; 383SELECT * FROM t2 ORDER BY c1; 384SELECT * FROM t3 ORDER BY c1; 385DELETE FROM t1 WHERE c1=1; 386DELETE FROM t1 WHERE c1=0; 387DELETE FROM t1 WHERE c1=NULL; 388DELETE FROM t1 WHERE c1=6 OR c1=7; 389DELETE FROM t1 WHERE c2=3; 390SELECT * FROM t1 ORDER BY c1; 391SELECT * FROM t2 ORDER BY c1; 392SELECT * FROM t3 ORDER BY c1; 393DROP TABLE t1,t2,t3; 394CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER); 395INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 396CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER); 397CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER); 398INSERT INTO t3 VALUES(1,0); 399delimiter //; 400CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 401INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 402UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 403END// 404DELIMITER ;// 405REPLACE INTO t1(c1,c2) VALUES(12,12); 406REPLACE INTO t1(c1,c2) VALUES(1,11); 407REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 408SELECT * FROM t1 ORDER BY c1; 409SELECT * FROM t2 ORDER BY c1; 410SELECT * FROM t3 ORDER BY c1; 411DELETE FROM t1 WHERE c1=1; 412DELETE FROM t1 WHERE c1=0; 413DELETE FROM t1 WHERE c1=NULL; 414DELETE FROM t1 WHERE c1=6 OR c1=7; 415DELETE FROM t1 WHERE c2=3; 416SELECT * FROM t1 ORDER BY c1; 417SELECT * FROM t2 ORDER BY c1; 418SELECT * FROM t3 ORDER BY c1; 419DROP TABLE t1,t2,t3; 420CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT); 421INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 422CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT); 423CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT); 424INSERT INTO t3 VALUES(1,0); 425delimiter //; 426CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 427INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 428UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 429END// 430DELIMITER ;// 431REPLACE INTO t1(c1,c2) VALUES(12,12); 432REPLACE INTO t1(c1,c2) VALUES(1,11); 433REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 434SELECT * FROM t1 ORDER BY c1; 435SELECT * FROM t2 ORDER BY c1; 436SELECT * FROM t3 ORDER BY c1; 437DELETE FROM t1 WHERE c1=1; 438DELETE FROM t1 WHERE c1=0; 439DELETE FROM t1 WHERE c1=NULL; 440DELETE FROM t1 WHERE c1=6 OR c1=7; 441DELETE FROM t1 WHERE c2=3; 442SELECT * FROM t1 ORDER BY c1; 443SELECT * FROM t2 ORDER BY c1; 444SELECT * FROM t3 ORDER BY c1; 445DROP TABLE t1,t2,t3; 446CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL); 447INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 448CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL); 449CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL); 450INSERT INTO t3 VALUES(1,0); 451delimiter //; 452CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 453INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 454UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 455END// 456DELIMITER ;// 457REPLACE INTO t1(c1,c2) VALUES(12,12); 458REPLACE INTO t1(c1,c2) VALUES(1,11); 459REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 460SELECT * FROM t1 ORDER BY c1; 461SELECT * FROM t2 ORDER BY c1; 462SELECT * FROM t3 ORDER BY c1; 463DELETE FROM t1 WHERE c1=1; 464DELETE FROM t1 WHERE c1=0; 465DELETE FROM t1 WHERE c1=NULL; 466DELETE FROM t1 WHERE c1=6 OR c1=7; 467DELETE FROM t1 WHERE c2=3; 468SELECT * FROM t1 ORDER BY c1; 469SELECT * FROM t2 ORDER BY c1; 470SELECT * FROM t3 ORDER BY c1; 471DROP TABLE t1,t2,t3; 472CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE); 473INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 474CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE); 475CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE); 476INSERT INTO t3 VALUES(1,0); 477delimiter //; 478CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 479INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 480UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 481END// 482DELIMITER ;// 483REPLACE INTO t1(c1,c2) VALUES(12,12); 484REPLACE INTO t1(c1,c2) VALUES(1,11); 485REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 486SELECT * FROM t1 ORDER BY c1; 487SELECT * FROM t2 ORDER BY c1; 488SELECT * FROM t3 ORDER BY c1; 489DELETE FROM t1 WHERE c1=1; 490DELETE FROM t1 WHERE c1=0; 491DELETE FROM t1 WHERE c1=NULL; 492DELETE FROM t1 WHERE c1=6 OR c1=7; 493DELETE FROM t1 WHERE c2=3; 494SELECT * FROM t1 ORDER BY c1; 495SELECT * FROM t2 ORDER BY c1; 496SELECT * FROM t3 ORDER BY c1; 497DROP TABLE t1,t2,t3; 498CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT); 499INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 500CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT); 501CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT); 502INSERT INTO t3 VALUES(1,0); 503delimiter //; 504CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 505INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 506UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 507END// 508DELIMITER ;// 509REPLACE INTO t1(c1,c2) VALUES(12,12); 510REPLACE INTO t1(c1,c2) VALUES(1,11); 511REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 512SELECT * FROM t1 ORDER BY c1; 513SELECT * FROM t2 ORDER BY c1; 514SELECT * FROM t3 ORDER BY c1; 515DELETE FROM t1 WHERE c1=1; 516DELETE FROM t1 WHERE c1=0; 517DELETE FROM t1 WHERE c1=NULL; 518DELETE FROM t1 WHERE c1=6 OR c1=7; 519DELETE FROM t1 WHERE c2=3; 520SELECT * FROM t1 ORDER BY c1; 521SELECT * FROM t2 ORDER BY c1; 522SELECT * FROM t3 ORDER BY c1; 523DROP TABLE t1,t2,t3; 524CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL); 525INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 526CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL); 527CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL); 528INSERT INTO t3 VALUES(1,0); 529delimiter //; 530CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 531INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 532UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 533END// 534DELIMITER ;// 535REPLACE INTO t1(c1,c2) VALUES(12,12); 536REPLACE INTO t1(c1,c2) VALUES(1,11); 537REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 538SELECT * FROM t1 ORDER BY c1; 539SELECT * FROM t2 ORDER BY c1; 540SELECT * FROM t3 ORDER BY c1; 541DELETE FROM t1 WHERE c1=1; 542DELETE FROM t1 WHERE c1=0; 543DELETE FROM t1 WHERE c1=NULL; 544DELETE FROM t1 WHERE c1=6 OR c1=7; 545DELETE FROM t1 WHERE c2=3; 546SELECT * FROM t1 ORDER BY c1; 547SELECT * FROM t2 ORDER BY c1; 548SELECT * FROM t3 ORDER BY c1; 549DROP TABLE t1,t2,t3; 550CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC); 551INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10); 552CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC); 553CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC); 554INSERT INTO t3 VALUES(1,0); 555delimiter //; 556CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN 557INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0); 558UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1; 559END// 560DELIMITER ;// 561REPLACE INTO t1(c1,c2) VALUES(12,12); 562REPLACE INTO t1(c1,c2) VALUES(1,11); 563REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11); 564SELECT * FROM t1 ORDER BY c1; 565SELECT * FROM t2 ORDER BY c1; 566SELECT * FROM t3 ORDER BY c1; 567DELETE FROM t1 WHERE c1=1; 568DELETE FROM t1 WHERE c1=0; 569DELETE FROM t1 WHERE c1=NULL; 570DELETE FROM t1 WHERE c1=6 OR c1=7; 571DELETE FROM t1 WHERE c2=3; 572SELECT * FROM t1 ORDER BY c1; 573SELECT * FROM t2 ORDER BY c1; 574SELECT * FROM t3 ORDER BY c1; 575DROP TABLE t1,t2,t3; 576 577