1# The author disclaims copyright to this source code. In place of 2# a legal notice, here is a blessing: 3# 4# May you do good and not evil. 5# May you find forgiveness for yourself and forgive others. 6# May you share freely, never taking more than you give. 7# 8#*********************************************************************** 9# 10# Regression testing of FOR EACH ROW table triggers 11# 12# 1. Trigger execution order tests. 13# These tests ensure that BEFORE and AFTER triggers are fired at the correct 14# times relative to each other and the triggering statement. 15# 16# trigger2-1.1.*: ON UPDATE trigger execution model. 17# trigger2-1.2.*: DELETE trigger execution model. 18# trigger2-1.3.*: INSERT trigger execution model. 19# 20# 2. Trigger program execution tests. 21# These tests ensure that trigger programs execute correctly (ie. that a 22# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT 23# statements, and combinations thereof). 24# 25# 3. Selective trigger execution 26# This tests that conditional triggers (ie. UPDATE OF triggers and triggers 27# with WHEN clauses) are fired only fired when they are supposed to be. 28# 29# trigger2-3.1: UPDATE OF triggers 30# trigger2-3.2: WHEN clause 31# 32# 4. Cascaded trigger execution 33# Tests that trigger-programs may cause other triggers to fire. Also that a 34# trigger-program is never executed recursively. 35# 36# trigger2-4.1: Trivial cascading trigger 37# trigger2-4.2: Trivial recursive trigger handling 38# 39# 5. Count changes behaviour. 40# Verify that rows altered by triggers are not included in the return value 41# of the "count changes" interface. 42# 43# 6. ON CONFLICT clause handling 44# trigger2-6.1[a-f]: INSERT statements 45# trigger2-6.2[a-f]: UPDATE statements 46# 47# 7. Triggers on views fire correctly. 48# 49 50set testdir [file dirname $argv0] 51source $testdir/tester.tcl 52 53# 1. 54set ii 0 55foreach tbl_defn [ list \ 56 {CREATE TABLE tbl (a, b);} \ 57 {CREATE TEMP TABLE tbl (a, b);} \ 58 {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \ 59 {CREATE TABLE tbl (a, b PRIMARY KEY);} \ 60 {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} ] { 61 incr ii 62 catchsql { DROP INDEX tbl_idx; } 63 catchsql { 64 DROP TABLE rlog; 65 DROP TABLE clog; 66 DROP TABLE tbl; 67 DROP TABLE other_tbl; 68 } 69 70 execsql $tbl_defn 71 72 execsql { 73 INSERT INTO tbl VALUES(1, 2); 74 INSERT INTO tbl VALUES(3, 4); 75 76 CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 77 CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 78 79 CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 80 BEGIN 81 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 82 old.a, old.b, 83 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 84 new.a, new.b); 85 END; 86 87 CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 88 BEGIN 89 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 90 old.a, old.b, 91 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 92 new.a, new.b); 93 END; 94 95 CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW 96 WHEN old.a = 1 97 BEGIN 98 INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 99 old.a, old.b, 100 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 101 new.a, new.b); 102 END; 103 } 104 105 do_test trigger2-1.1.$ii { 106 execsql { 107 UPDATE tbl SET a = a * 10, b = b * 10; 108 SELECT * FROM rlog ORDER BY idx; 109 SELECT * FROM clog ORDER BY idx; 110 } 111 } [list 1 1 2 4 6 10 20 \ 112 2 1 2 13 24 10 20 \ 113 3 3 4 13 24 30 40 \ 114 4 3 4 40 60 30 40 \ 115 1 1 2 13 24 10 20 ] 116 117 execsql { 118 DELETE FROM rlog; 119 DELETE FROM tbl; 120 INSERT INTO tbl VALUES (100, 100); 121 INSERT INTO tbl VALUES (300, 200); 122 CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW 123 BEGIN 124 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 125 old.a, old.b, 126 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 127 0, 0); 128 END; 129 130 CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW 131 BEGIN 132 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 133 old.a, old.b, 134 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 135 0, 0); 136 END; 137 } 138 do_test trigger2-1.2.$ii { 139 execsql { 140 DELETE FROM tbl; 141 SELECT * FROM rlog; 142 } 143 } [list 1 100 100 400 300 0 0 \ 144 2 100 100 300 200 0 0 \ 145 3 300 200 300 200 0 0 \ 146 4 300 200 0 0 0 0 ] 147 148 execsql { 149 DELETE FROM rlog; 150 CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW 151 BEGIN 152 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 153 0, 0, 154 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 155 new.a, new.b); 156 END; 157 158 CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW 159 BEGIN 160 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 161 0, 0, 162 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 163 new.a, new.b); 164 END; 165 } 166 do_test trigger2-1.3.$ii { 167 execsql { 168 169 CREATE TABLE other_tbl(a, b); 170 INSERT INTO other_tbl VALUES(1, 2); 171 INSERT INTO other_tbl VALUES(3, 4); 172 -- INSERT INTO tbl SELECT * FROM other_tbl; 173 INSERT INTO tbl VALUES(5, 6); 174 DROP TABLE other_tbl; 175 176 SELECT * FROM rlog; 177 } 178 } [list 1 0 0 0 0 5 6 \ 179 2 0 0 5 6 5 6 ] 180} 181catchsql { 182 DROP TABLE rlog; 183 DROP TABLE clog; 184 DROP TABLE tbl; 185 DROP TABLE other_tbl; 186} 187 188# 2. 189set ii 0 190foreach tr_program [ list \ 191 {UPDATE tbl SET b = old.b;} \ 192 {INSERT INTO log VALUES(new.c, 2, 3);} \ 193 {DELETE FROM log WHERE a = 1;} \ 194 {INSERT INTO tbl VALUES(500, new.b * 10, 700); 195 UPDATE tbl SET c = old.c; 196 DELETE FROM log;} \ 197 {INSERT INTO log select * from tbl;} 198 ] \ 199{ 200 foreach test_varset [ list \ 201 { 202 set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 203 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 204 set newC 10 205 set newB 2 206 set newA 1 207 set oldA 1 208 set oldB 2 209 set oldC 3 210 } \ 211 { 212 set statement {DELETE FROM tbl WHERE a = 1;} 213 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 214 set oldA 1 215 set oldB 2 216 set oldC 3 217 } \ 218 { 219 set statement {INSERT INTO tbl VALUES(1, 2, 3);} 220 set newA 1 221 set newB 2 222 set newC 3 223 } 224 ] \ 225 { 226 set statement {} 227 set prep {} 228 set newA {''} 229 set newB {''} 230 set newC {''} 231 set oldA {''} 232 set oldB {''} 233 set oldC {''} 234 235 incr ii 236 237 eval $test_varset 238 239 set statement_type [string range $statement 0 5] 240 set tr_program_fixed $tr_program 241 if {$statement_type == "DELETE"} { 242 regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 243 regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 244 regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 245 } 246 if {$statement_type == "INSERT"} { 247 regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 248 regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 249 regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 250 } 251 252 253 set tr_program_cooked $tr_program 254 regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 255 regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 256 regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 257 regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 258 regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 259 regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 260 261 catchsql { 262 DROP TABLE tbl; 263 DROP TABLE log; 264 } 265 execsql { 266 CREATE TABLE tbl(a PRIMARY KEY, b, c); 267 CREATE TABLE log(a, b, c); 268 } 269 270 set query {SELECT * FROM tbl; SELECT * FROM log;} 271 set prep "$prep; INSERT INTO log VALUES(1, 2, 3); INSERT INTO log VALUES(10, 20, 30);" 272 273# Check execution of BEFORE programs: 274 275 set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] 276 277 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 278 execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;" 279 280 do_test trigger2-2-$ii-before "execsql {$statement $query}" $before_data 281 282 execsql "DROP TRIGGER the_trigger;" 283 execsql "DELETE FROM tbl; DELETE FROM log;" 284 285# Check execution of AFTER programs 286 set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] 287 288 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 289 290 execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;" 291 292 do_test trigger2-2-$ii-after "execsql {$statement $query}" $after_data 293 execsql "DROP TRIGGER the_trigger;" 294 } 295} 296catchsql { 297 DROP TABLE tbl; 298 DROP TABLE log; 299} 300 301# 3. 302 303# trigger2-3.1: UPDATE OF triggers 304execsql { 305 CREATE TABLE tbl (a, b, c, d); 306 CREATE TABLE log (a); 307 INSERT INTO log VALUES (0); 308 INSERT INTO tbl VALUES (0, 0, 0, 0); 309 INSERT INTO tbl VALUES (1, 0, 0, 0); 310 CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl 311 BEGIN 312 UPDATE log SET a = a + 1; 313 END; 314} 315do_test trigger2-3.1 { 316 execsql { 317 UPDATE tbl SET b = 1, c = 10; -- 2 318 UPDATE tbl SET b = 10; -- 0 319 UPDATE tbl SET d = 4 WHERE a = 0; --1 320 UPDATE tbl SET a = 4, b = 10; --0 321 SELECT * FROM log; 322 } 323} {3} 324execsql { 325 DROP TABLE tbl; 326 DROP TABLE log; 327} 328 329# trigger2-3.2: WHEN clause 330set when_triggers [ list \ 331 {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \ 332 {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ] 333 334execsql { 335 CREATE TABLE tbl (a, b, c, d); 336 CREATE TABLE log (a); 337 INSERT INTO log VALUES (0); 338} 339 340foreach trig $when_triggers { 341 execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;" 342} 343 344do_test trigger2-3.2 { 345 execsql { 346 347 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 348 SELECT * FROM log; 349 UPDATE log SET a = 0; 350 351 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0 352 SELECT * FROM log; 353 UPDATE log SET a = 0; 354 355 INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1 356 SELECT * FROM log; 357 UPDATE log SET a = 0; 358 } 359} {1 0 1} 360execsql { 361 DROP TABLE tbl; 362 DROP TABLE log; 363} 364 365# Simple cascaded trigger 366execsql { 367 CREATE TABLE tblA(a, b); 368 CREATE TABLE tblB(a, b); 369 CREATE TABLE tblC(a, b); 370 371 CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN 372 INSERT INTO tblB values(new.a, new.b); 373 END; 374 375 CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN 376 INSERT INTO tblC values(new.a, new.b); 377 END; 378} 379do_test trigger2-4.1 { 380 execsql { 381 INSERT INTO tblA values(1, 2); 382 SELECT * FROM tblA; 383 SELECT * FROM tblB; 384 SELECT * FROM tblC; 385 } 386} {1 2 1 2 1 2} 387execsql { 388 DROP TABLE tblA; 389 DROP TABLE tblB; 390 DROP TABLE tblC; 391} 392 393# Simple recursive trigger 394execsql { 395 CREATE TABLE tbl(a, b, c); 396 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 397 BEGIN 398 INSERT INTO tbl VALUES (new.a, new.b, new.c); 399 END; 400} 401do_test trigger2-4.2 { 402 execsql { 403 INSERT INTO tbl VALUES (1, 2, 3); 404 select * from tbl; 405 } 406} {1 2 3 1 2 3} 407execsql { 408 DROP TABLE tbl; 409} 410 411# 5. 412execsql { 413 CREATE TABLE tbl(a, b, c); 414 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 415 BEGIN 416 INSERT INTO tbl VALUES (1, 2, 3); 417 INSERT INTO tbl VALUES (2, 2, 3); 418 UPDATE tbl set b = 10 WHERE a = 1; 419 DELETE FROM tbl WHERE a = 1; 420 DELETE FROM tbl; 421 END; 422} 423do_test trigger2-5 { 424 execsql { 425 INSERT INTO tbl VALUES(100, 200, 300); 426 } 427 db changes 428} {1} 429execsql { 430 DROP TABLE tbl; 431} 432 433# Handling of ON CONFLICT by INSERT statements inside triggers 434execsql { 435 CREATE TABLE tbl (a primary key, b, c); 436 CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN 437 INSERT OR IGNORE INTO tbl values (new.a, 0, 0); 438 END; 439} 440do_test trigger2-6.1a { 441 execsql { 442 BEGIN; 443 INSERT INTO tbl values (1, 2, 3); 444 SELECT * from tbl; 445 } 446} {1 2 3} 447do_test trigger2-6.1b { 448 catchsql { 449 INSERT OR ABORT INTO tbl values (2, 2, 3); 450 } 451} {1 {uniqueness constraint failed}} 452do_test trigger2-6.1c { 453 execsql { 454 SELECT * from tbl; 455 } 456} {1 2 3} 457do_test trigger2-6.1d { 458 catchsql { 459 INSERT OR FAIL INTO tbl values (2, 2, 3); 460 } 461} {1 {uniqueness constraint failed}} 462do_test trigger2-6.1e { 463 execsql { 464 SELECT * from tbl; 465 } 466} {1 2 3 2 2 3} 467do_test trigger2-6.1f { 468 execsql { 469 INSERT OR REPLACE INTO tbl values (2, 2, 3); 470 SELECT * from tbl; 471 } 472} {1 2 3 2 0 0} 473do_test trigger2-6.1g { 474 catchsql { 475 INSERT OR ROLLBACK INTO tbl values (3, 2, 3); 476 } 477} {1 {uniqueness constraint failed}} 478do_test trigger2-6.1h { 479 execsql { 480 SELECT * from tbl; 481 } 482} {} 483execsql {DELETE FROM tbl} 484 485 486# Handling of ON CONFLICT by UPDATE statements inside triggers 487execsql { 488 INSERT INTO tbl values (4, 2, 3); 489 INSERT INTO tbl values (6, 3, 4); 490 CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN 491 UPDATE OR IGNORE tbl SET a = new.a, c = 10; 492 END; 493} 494do_test trigger2-6.2a { 495 execsql { 496 BEGIN; 497 UPDATE tbl SET a = 1 WHERE a = 4; 498 SELECT * from tbl; 499 } 500} {1 2 10 6 3 4} 501do_test trigger2-6.2b { 502 catchsql { 503 UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; 504 } 505} {1 {uniqueness constraint failed}} 506do_test trigger2-6.2c { 507 execsql { 508 SELECT * from tbl; 509 } 510} {1 2 10 6 3 4} 511do_test trigger2-6.2d { 512 catchsql { 513 UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; 514 } 515} {1 {uniqueness constraint failed}} 516do_test trigger2-6.2e { 517 execsql { 518 SELECT * from tbl; 519 } 520} {4 2 10 6 3 4} 521do_test trigger2-6.2f.1 { 522 execsql { 523 UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; 524 SELECT * from tbl; 525 } 526} {1 3 10} 527do_test trigger2-6.2f.2 { 528 execsql { 529 INSERT INTO tbl VALUES (2, 3, 4); 530 SELECT * FROM tbl; 531 } 532} {1 3 10 2 3 4} 533do_test trigger2-6.2g { 534 catchsql { 535 UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; 536 } 537} {1 {uniqueness constraint failed}} 538do_test trigger2-6.2h { 539 execsql { 540 SELECT * from tbl; 541 } 542} {4 2 3 6 3 4} 543execsql { 544 DROP TABLE tbl; 545} 546 547# 7. Triggers on views 548do_test trigger2-7.1 { 549 execsql { 550 CREATE TABLE ab(a, b); 551 CREATE TABLE cd(c, d); 552 INSERT INTO ab VALUES (1, 2); 553 INSERT INTO ab VALUES (0, 0); 554 INSERT INTO cd VALUES (3, 4); 555 556 CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 557 olda, oldb, oldc, oldd, newa, newb, newc, newd); 558 559 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 560 561 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN 562 INSERT INTO tlog VALUES(NULL, 563 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 564 END; 565 CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN 566 INSERT INTO tlog VALUES(NULL, 567 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 568 END; 569 570 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN 571 INSERT INTO tlog VALUES(NULL, 572 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 573 END; 574 CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN 575 INSERT INTO tlog VALUES(NULL, 576 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 577 END; 578 579 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN 580 INSERT INTO tlog VALUES(NULL, 581 0, 0, 0, 0, new.a, new.b, new.c, new.d); 582 END; 583 CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN 584 INSERT INTO tlog VALUES(NULL, 585 0, 0, 0, 0, new.a, new.b, new.c, new.d); 586 END; 587 } 588} {} 589 590do_test trigger2-7.2 { 591 execsql { 592 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 593 DELETE FROM abcd WHERE a = 1; 594 INSERT INTO abcd VALUES(10, 20, 30, 40); 595 SELECT * FROM tlog; 596 } 597} [ list 1 1 2 3 4 100 25 3 4 \ 598 2 1 2 3 4 100 25 3 4 \ 599 3 1 2 3 4 0 0 0 0 \ 600 4 1 2 3 4 0 0 0 0 \ 601 5 0 0 0 0 10 20 30 40 \ 602 6 0 0 0 0 10 20 30 40 ] 603 604finish_test 605