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