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