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. & 8. Triggers on views fire correctly. 48# 49 50set testdir [file dirname $argv0] 51source $testdir/tester.tcl 52set testprefix trigger2 53ifcapable {!trigger} { 54 finish_test 55 return 56} 57 58# The tests in this file were written before SQLite supported recursive 59# trigger invocation, and some tests depend on that to pass. So disable 60# recursive triggers for this file. 61catchsql { pragma recursive_triggers = off } 62 63# 1. 64ifcapable subquery { 65 set ii 0 66 set tbl_definitions [list \ 67 {CREATE TABLE tbl (a, b);} \ 68 {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \ 69 {CREATE TABLE tbl (a, b PRIMARY KEY);} \ 70 {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} \ 71 ] 72 ifcapable tempdb { 73 lappend tbl_definitions \ 74 {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 75 lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);} 76 lappend tbl_definitions \ 77 {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);} 78 } 79 foreach tbl_defn $tbl_definitions { 80 incr ii 81 catchsql { DROP INDEX tbl_idx; } 82 catchsql { 83 DROP TABLE rlog; 84 DROP TABLE clog; 85 DROP TABLE tbl; 86 DROP TABLE other_tbl; 87 } 88 89 execsql $tbl_defn 90 91 execsql { 92 INSERT INTO tbl VALUES(1, 2); 93 INSERT INTO tbl VALUES(3, 4); 94 95 CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 96 CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 97 98 CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 99 BEGIN 100 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 101 old.a, old.b, 102 (SELECT coalesce(sum(a),0) FROM tbl), 103 (SELECT coalesce(sum(b),0) FROM tbl), 104 new.a, new.b); 105 END; 106 107 CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 108 BEGIN 109 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 110 old.a, old.b, 111 (SELECT coalesce(sum(a),0) FROM tbl), 112 (SELECT coalesce(sum(b),0) FROM tbl), 113 new.a, new.b); 114 END; 115 116 CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW 117 WHEN old.a = 1 118 BEGIN 119 INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 120 old.a, old.b, 121 (SELECT coalesce(sum(a),0) FROM tbl), 122 (SELECT coalesce(sum(b),0) FROM tbl), 123 new.a, new.b); 124 END; 125 } 126 127 do_test trigger2-1.$ii.1 { 128 set r {} 129 foreach v [execsql { 130 UPDATE tbl SET a = a * 10, b = b * 10; 131 SELECT * FROM rlog ORDER BY idx; 132 SELECT * FROM clog ORDER BY idx; 133 }] { 134 lappend r [expr {int($v)}] 135 } 136 set r 137 } [list 1 1 2 4 6 10 20 \ 138 2 1 2 13 24 10 20 \ 139 3 3 4 13 24 30 40 \ 140 4 3 4 40 60 30 40 \ 141 1 1 2 13 24 10 20 ] 142 143 execsql { 144 DELETE FROM rlog; 145 DELETE FROM tbl; 146 INSERT INTO tbl VALUES (100, 100); 147 INSERT INTO tbl VALUES (300, 200); 148 CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW 149 BEGIN 150 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 151 old.a, old.b, 152 (SELECT coalesce(sum(a),0) FROM tbl), 153 (SELECT coalesce(sum(b),0) FROM tbl), 154 0, 0); 155 END; 156 157 CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW 158 BEGIN 159 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 160 old.a, old.b, 161 (SELECT coalesce(sum(a),0) FROM tbl), 162 (SELECT coalesce(sum(b),0) FROM tbl), 163 0, 0); 164 END; 165 } 166 do_test trigger2-1.$ii.2 { 167 set r {} 168 foreach v [execsql { 169 DELETE FROM tbl; 170 SELECT * FROM rlog; 171 }] { 172 lappend r [expr {int($v)}] 173 } 174 set r 175 } [list 1 100 100 400 300 0 0 \ 176 2 100 100 300 200 0 0 \ 177 3 300 200 300 200 0 0 \ 178 4 300 200 0 0 0 0 ] 179 180 execsql { 181 DELETE FROM rlog; 182 CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW 183 BEGIN 184 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 185 0, 0, 186 (SELECT coalesce(sum(a),0) FROM tbl), 187 (SELECT coalesce(sum(b),0) FROM tbl), 188 new.a, new.b); 189 END; 190 191 CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW 192 BEGIN 193 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 194 0, 0, 195 (SELECT coalesce(sum(a),0) FROM tbl), 196 (SELECT coalesce(sum(b),0) FROM tbl), 197 new.a, new.b); 198 END; 199 } 200 do_test trigger2-1.$ii.3 { 201 execsql { 202 203 CREATE TABLE other_tbl(a, b); 204 INSERT INTO other_tbl VALUES(1, 2); 205 INSERT INTO other_tbl VALUES(3, 4); 206 -- INSERT INTO tbl SELECT * FROM other_tbl; 207 INSERT INTO tbl VALUES(5, 6); 208 DROP TABLE other_tbl; 209 210 SELECT * FROM rlog; 211 } 212 } [list 1 0 0 0 0 5 6 \ 213 2 0 0 5 6 5 6 ] 214 215 integrity_check trigger2-1.$ii.4 216 } 217 catchsql { 218 DROP TABLE rlog; 219 DROP TABLE clog; 220 DROP TABLE tbl; 221 DROP TABLE other_tbl; 222 } 223} 224 225# 2. 226set ii 0 227foreach tr_program { 228 {UPDATE tbl SET b = old.b;} 229 {INSERT INTO log VALUES(new.c, 2, 3);} 230 {DELETE FROM log WHERE a = 1;} 231 {INSERT INTO tbl VALUES(500, new.b * 10, 700); 232 UPDATE tbl SET c = old.c; 233 DELETE FROM log;} 234 {INSERT INTO log select * from tbl;} 235} { 236 foreach test_varset [ list \ 237 { 238 set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 239 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 240 set newC 10 241 set newB 2 242 set newA 1 243 set oldA 1 244 set oldB 2 245 set oldC 3 246 } \ 247 { 248 set statement {DELETE FROM tbl WHERE a = 1;} 249 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 250 set oldA 1 251 set oldB 2 252 set oldC 3 253 } \ 254 { 255 set statement {INSERT INTO tbl VALUES(1, 2, 3);} 256 set newA 1 257 set newB 2 258 set newC 3 259 } 260 ] \ 261 { 262 set statement {} 263 set prep {} 264 set newA {''} 265 set newB {''} 266 set newC {''} 267 set oldA {''} 268 set oldB {''} 269 set oldC {''} 270 271 incr ii 272 273 eval $test_varset 274 275 set statement_type [string range $statement 0 5] 276 set tr_program_fixed $tr_program 277 if {$statement_type == "DELETE"} { 278 regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 279 regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 280 regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 281 } 282 if {$statement_type == "INSERT"} { 283 regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 284 regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 285 regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 286 } 287 288 289 set tr_program_cooked $tr_program 290 regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 291 regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 292 regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 293 regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 294 regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 295 regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 296 297 catchsql { 298 DROP TABLE tbl; 299 DROP TABLE log; 300 } 301 302 execsql { 303 CREATE TABLE tbl(a PRIMARY KEY, b, c); 304 CREATE TABLE log(a, b, c); 305 } 306 307 set query {SELECT * FROM tbl; SELECT * FROM log;} 308 set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\ 309 INSERT INTO log VALUES(10, 20, 30);" 310 311# Check execution of BEFORE programs: 312 313 set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] 314 315 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 316 execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\ 317 ON tbl BEGIN $tr_program_fixed END;" 318 319 do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data 320 321 execsql "DROP TRIGGER the_trigger;" 322 execsql "DELETE FROM tbl; DELETE FROM log;" 323 324# Check execution of AFTER programs 325 set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] 326 327 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 328 execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\ 329 ON tbl BEGIN $tr_program_fixed END;" 330 331 do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data 332 execsql "DROP TRIGGER the_trigger;" 333 334 integrity_check trigger2-2.$ii-integrity 335 } 336} 337catchsql { 338 DROP TABLE tbl; 339 DROP TABLE log; 340} 341 342# 3. 343 344# trigger2-3.1: UPDATE OF triggers 345execsql { 346 CREATE TABLE tbl (a, b, c, d); 347 CREATE TABLE log (a); 348 INSERT INTO log VALUES (0); 349 INSERT INTO tbl VALUES (0, 0, 0, 0); 350 INSERT INTO tbl VALUES (1, 0, 0, 0); 351 CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl 352 BEGIN 353 UPDATE log SET a = a + 1; 354 END; 355} 356do_test trigger2-3.1 { 357 execsql { 358 UPDATE tbl SET b = 1, c = 10; -- 2 359 UPDATE tbl SET b = 10; -- 0 360 UPDATE tbl SET d = 4 WHERE a = 0; --1 361 UPDATE tbl SET a = 4, b = 10; --0 362 SELECT * FROM log; 363 } 364} {3} 365execsql { 366 DROP TABLE tbl; 367 DROP TABLE log; 368} 369 370# trigger2-3.2: WHEN clause 371set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}] 372ifcapable subquery { 373 lappend when_triggers \ 374 {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} 375} 376 377execsql { 378 CREATE TABLE tbl (a, b, c, d); 379 CREATE TABLE log (a); 380 INSERT INTO log VALUES (0); 381} 382 383foreach trig $when_triggers { 384 execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;" 385} 386 387ifcapable subquery { 388 set t232 {1 0 1} 389} else { 390 set t232 {0 0 1} 391} 392do_test trigger2-3.2 { 393 execsql { 394 395 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery) 396 SELECT * FROM log; 397 UPDATE log SET a = 0; 398 399 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0 400 SELECT * FROM log; 401 UPDATE log SET a = 0; 402 403 INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1 404 SELECT * FROM log; 405 UPDATE log SET a = 0; 406 } 407} $t232 408execsql { 409 DROP TABLE tbl; 410 DROP TABLE log; 411} 412integrity_check trigger2-3.3 413 414# Simple cascaded trigger 415execsql { 416 CREATE TABLE tblA(a, b); 417 CREATE TABLE tblB(a, b); 418 CREATE TABLE tblC(a, b); 419 420 CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN 421 INSERT INTO tblB values(new.a, new.b); 422 END; 423 424 CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN 425 INSERT INTO tblC values(new.a, new.b); 426 END; 427} 428do_test trigger2-4.1 { 429 execsql { 430 INSERT INTO tblA values(1, 2); 431 SELECT * FROM tblA; 432 SELECT * FROM tblB; 433 SELECT * FROM tblC; 434 } 435} {1 2 1 2 1 2} 436execsql { 437 DROP TABLE tblA; 438 DROP TABLE tblB; 439 DROP TABLE tblC; 440} 441 442# Simple recursive trigger 443execsql { 444 CREATE TABLE tbl(a, b, c); 445 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 446 BEGIN 447 INSERT INTO tbl VALUES (new.a, new.b, new.c); 448 END; 449} 450do_test trigger2-4.2 { 451 execsql { 452 INSERT INTO tbl VALUES (1, 2, 3); 453 select * from tbl; 454 } 455} {1 2 3 1 2 3} 456execsql { 457 DROP TABLE tbl; 458} 459 460# 5. 461execsql { 462 CREATE TABLE tbl(a, b, c); 463 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 464 BEGIN 465 INSERT INTO tbl VALUES (1, 2, 3); 466 INSERT INTO tbl VALUES (2, 2, 3); 467 UPDATE tbl set b = 10 WHERE a = 1; 468 DELETE FROM tbl WHERE a = 1; 469 DELETE FROM tbl; 470 END; 471} 472do_test trigger2-5 { 473 execsql { 474 INSERT INTO tbl VALUES(100, 200, 300); 475 } 476 db changes 477} {1} 478execsql { 479 DROP TABLE tbl; 480} 481 482ifcapable conflict { 483 # Handling of ON CONFLICT by INSERT statements inside triggers 484 execsql { 485 CREATE TABLE tbl (a primary key, b, c); 486 CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN 487 INSERT OR IGNORE INTO tbl values (new.a, 0, 0); 488 END; 489 } 490 do_test trigger2-6.1a { 491 execsql { 492 BEGIN; 493 INSERT INTO tbl values (1, 2, 3); 494 SELECT * from tbl; 495 } 496 } {1 2 3} 497 do_test trigger2-6.1b { 498 catchsql { 499 INSERT OR ABORT INTO tbl values (2, 2, 3); 500 } 501 } {1 {UNIQUE constraint failed: tbl.a}} 502 do_test trigger2-6.1c { 503 execsql { 504 SELECT * from tbl; 505 } 506 } {1 2 3} 507 do_test trigger2-6.1d { 508 catchsql { 509 INSERT OR FAIL INTO tbl values (2, 2, 3); 510 } 511 } {1 {UNIQUE constraint failed: tbl.a}} 512 do_test trigger2-6.1e { 513 execsql { 514 SELECT * from tbl; 515 } 516 } {1 2 3 2 2 3} 517 do_test trigger2-6.1f { 518 execsql { 519 INSERT OR REPLACE INTO tbl values (2, 2, 3); 520 SELECT * from tbl; 521 } 522 } {1 2 3 2 0 0} 523 do_test trigger2-6.1g { 524 catchsql { 525 INSERT OR ROLLBACK INTO tbl values (3, 2, 3); 526 } 527 } {1 {UNIQUE constraint failed: tbl.a}} 528 do_test trigger2-6.1h { 529 execsql { 530 SELECT * from tbl; 531 } 532 } {} 533 execsql {DELETE FROM tbl} 534 535 536 # Handling of ON CONFLICT by UPDATE statements inside triggers 537 execsql { 538 INSERT INTO tbl values (4, 2, 3); 539 INSERT INTO tbl values (6, 3, 4); 540 CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN 541 UPDATE OR IGNORE tbl SET a = new.a, c = 10; 542 END; 543 } 544 do_test trigger2-6.2a { 545 execsql { 546 BEGIN; 547 UPDATE tbl SET a = 1 WHERE a = 4; 548 SELECT * from tbl; 549 } 550 } {1 2 10 6 3 4} 551 do_test trigger2-6.2b { 552 catchsql { 553 UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; 554 } 555 } {1 {UNIQUE constraint failed: tbl.a}} 556 do_test trigger2-6.2c { 557 execsql { 558 SELECT * from tbl; 559 } 560 } {1 2 10 6 3 4} 561 do_test trigger2-6.2d { 562 catchsql { 563 UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; 564 } 565 } {1 {UNIQUE constraint failed: tbl.a}} 566 do_test trigger2-6.2e { 567 execsql { 568 SELECT * from tbl; 569 } 570 } {4 2 10 6 3 4} 571 do_test trigger2-6.2f.1 { 572 execsql { 573 UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; 574 SELECT * from tbl; 575 } 576 } {1 3 10} 577 do_test trigger2-6.2f.2 { 578 execsql { 579 INSERT INTO tbl VALUES (2, 3, 4); 580 SELECT * FROM tbl; 581 } 582 } {1 3 10 2 3 4} 583 do_test trigger2-6.2g { 584 catchsql { 585 UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; 586 } 587 } {1 {UNIQUE constraint failed: tbl.a}} 588 do_test trigger2-6.2h { 589 execsql { 590 SELECT * from tbl; 591 } 592 } {4 2 3 6 3 4} 593 execsql { 594 DROP TABLE tbl; 595 } 596} ; # ifcapable conflict 597 598# 7. Triggers on views 599ifcapable view { 600 601do_test trigger2-7.1 { 602 execsql { 603 CREATE TABLE ab(a, b); 604 CREATE TABLE cd(c, d); 605 INSERT INTO ab VALUES (1, 2); 606 INSERT INTO ab VALUES (0, 0); 607 INSERT INTO cd VALUES (3, 4); 608 609 CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 610 olda, oldb, oldc, oldd, newa, newb, newc, newd); 611 612 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 613 614 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN 615 INSERT INTO tlog VALUES(NULL, 616 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 617 END; 618 CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN 619 INSERT INTO tlog VALUES(NULL, 620 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 621 END; 622 623 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN 624 INSERT INTO tlog VALUES(NULL, 625 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 626 END; 627 CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN 628 INSERT INTO tlog VALUES(NULL, 629 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 630 END; 631 632 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN 633 INSERT INTO tlog VALUES(NULL, 634 0, 0, 0, 0, new.a, new.b, new.c, new.d); 635 END; 636 CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN 637 INSERT INTO tlog VALUES(NULL, 638 0, 0, 0, 0, new.a, new.b, new.c, new.d); 639 END; 640 } 641} {}; 642 643do_test trigger2-7.2 { 644 execsql { 645 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 646 DELETE FROM abcd WHERE a = 1; 647 INSERT INTO abcd VALUES(10, 20, 30, 40); 648 SELECT * FROM tlog; 649 } 650} [ list 1 1 2 3 4 100 25 3 4 \ 651 2 1 2 3 4 100 25 3 4 \ 652 3 1 2 3 4 0 0 0 0 \ 653 4 1 2 3 4 0 0 0 0 \ 654 5 0 0 0 0 10 20 30 40 \ 655 6 0 0 0 0 10 20 30 40 ] 656 657do_test trigger2-7.3 { 658 execsql { 659 DELETE FROM tlog; 660 INSERT INTO abcd VALUES(10, 20, 30, 40); 661 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 662 DELETE FROM abcd WHERE a = 1; 663 SELECT * FROM tlog; 664 } 665} [ list \ 666 1 0 0 0 0 10 20 30 40 \ 667 2 0 0 0 0 10 20 30 40 \ 668 3 1 2 3 4 100 25 3 4 \ 669 4 1 2 3 4 100 25 3 4 \ 670 5 1 2 3 4 0 0 0 0 \ 671 6 1 2 3 4 0 0 0 0 \ 672] 673do_test trigger2-7.4 { 674 execsql { 675 DELETE FROM tlog; 676 DELETE FROM abcd WHERE a = 1; 677 INSERT INTO abcd VALUES(10, 20, 30, 40); 678 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 679 SELECT * FROM tlog; 680 } 681} [ list \ 682 1 1 2 3 4 0 0 0 0 \ 683 2 1 2 3 4 0 0 0 0 \ 684 3 0 0 0 0 10 20 30 40 \ 685 4 0 0 0 0 10 20 30 40 \ 686 5 1 2 3 4 100 25 3 4 \ 687 6 1 2 3 4 100 25 3 4 \ 688] 689 690do_test trigger2-8.1 { 691 execsql { 692 CREATE TABLE t1(a,b,c); 693 INSERT INTO t1 VALUES(1,2,3); 694 CREATE VIEW v1 AS 695 SELECT a+b AS x, b+c AS y, a+c AS z FROM t1; 696 SELECT * FROM v1; 697 } 698} {3 5 4} 699do_test trigger2-8.2 { 700 execsql { 701 CREATE TABLE v1log(a,b,c,d,e,f); 702 CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN 703 INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL); 704 END; 705 DELETE FROM v1 WHERE x=1; 706 SELECT * FROM v1log; 707 } 708} {} 709do_test trigger2-8.3 { 710 execsql { 711 DELETE FROM v1 WHERE x=3; 712 SELECT * FROM v1log; 713 } 714} {3 {} 5 {} 4 {}} 715do_test trigger2-8.4 { 716 execsql { 717 INSERT INTO t1 VALUES(4,5,6); 718 DELETE FROM v1log; 719 DELETE FROM v1 WHERE y=11; 720 SELECT * FROM v1log; 721 } 722} {9 {} 11 {} 10 {}} 723do_test trigger2-8.5 { 724 execsql { 725 CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN 726 INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z); 727 END; 728 DELETE FROM v1log; 729 INSERT INTO v1 VALUES(1,2,3); 730 SELECT * FROM v1log; 731 } 732} {{} 1 {} 2 {} 3} 733do_test trigger2-8.6 { 734 execsql { 735 CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN 736 INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z); 737 END; 738 DELETE FROM v1log; 739 UPDATE v1 SET x=x+100, y=y+200, z=z+300; 740 SELECT * FROM v1log; 741 } 742} {3 103 5 205 4 304 9 109 11 211 10 310} 743 744# At one point the following was causing a segfault. 745do_test trigger2-9.1 { 746 execsql { 747 CREATE TABLE t3(a TEXT, b TEXT); 748 CREATE VIEW v3 AS SELECT t3.a FROM t3; 749 CREATE TRIGGER trig1 INSTEAD OF DELETE ON v3 BEGIN 750 SELECT 1; 751 END; 752 DELETE FROM v3 WHERE a = 1; 753 } 754} {} 755 756integrity_check trigger2-9.99 757 758# 2019-11-02 Problem found by TH3, related to generated column support. 759db close 760sqlite3 db :memory: 761do_execsql_test trigger2-10.1 { 762 CREATE TABLE t1(a,b,c,d); 763 CREATE VIEW v2(a,b,c,d) AS SELECT * FROM t1; 764 CREATE TRIGGER v2ins INSTEAD OF INSERT ON v2 BEGIN 765 INSERT INTO t1(a,b,c,d) VALUES(new.a, new.b, new.c, new.d); 766 END; 767 INSERT INTO v2(a,d) VALUES(11,14); 768 SELECT * FROM t1; 769} {11 {} {} 14} 770 771} ;# ifcapable view 772 773#------------------------------------------------------------------------- 774reset_db 775do_execsql_test 11.1 { 776 CREATE TABLE t1(a INT PRIMARY KEY, b, c REAL, d, e); 777 CREATE TABLE t2(a INT, b, c REAL, d, e, PRIMARY KEY(a,b)) WITHOUT ROWID; 778 CREATE UNIQUE INDEX t2c ON t2(c); 779 CREATE UNIQUE INDEX t2d ON t2(d); 780 CREATE UNIQUE INDEX t2e ON t2(e); 781} 782 783do_catchsql_test 11.2 { 784 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN 785 INSERT INTO t2(a,b,c,d,e) VALUES(91,NULL,93,94,?1) 786 ON CONFLICT(b,a) DO NOTHING 787 ON CONFLICT DO UPDATE SET b=?1; 788 END; 789} {1 {trigger cannot use variables}} 790 791 792finish_test 793 794