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