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