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 foreach tbl_defn { 61 {CREATE TEMP TABLE tbl (a, b);} 62 {CREATE TABLE tbl (a, b);} 63 {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} 64 {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);} 65 {CREATE TABLE tbl (a, b PRIMARY KEY);} 66 {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 67 {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 68 } { 69 incr ii 70 catchsql { DROP INDEX tbl_idx; } 71 catchsql { 72 DROP TABLE rlog; 73 DROP TABLE clog; 74 DROP TABLE tbl; 75 DROP TABLE other_tbl; 76 } 77 78 execsql $tbl_defn 79 80 execsql { 81 INSERT INTO tbl VALUES(1, 2); 82 INSERT INTO tbl VALUES(3, 4); 83 84 CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 85 CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 86 87 CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 88 BEGIN 89 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 90 old.a, old.b, 91 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 92 new.a, new.b); 93 END; 94 95 CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 96 BEGIN 97 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 98 old.a, old.b, 99 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 100 new.a, new.b); 101 END; 102 103 CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW 104 WHEN old.a = 1 105 BEGIN 106 INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 107 old.a, old.b, 108 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 109 new.a, new.b); 110 END; 111 } 112 113 do_test trigger2-1.$ii.1 { 114 set r {} 115 foreach v [execsql { 116 UPDATE tbl SET a = a * 10, b = b * 10; 117 SELECT * FROM rlog ORDER BY idx; 118 SELECT * FROM clog ORDER BY idx; 119 }] { 120 lappend r [expr {int($v)}] 121 } 122 set r 123 } [list 1 1 2 4 6 10 20 \ 124 2 1 2 13 24 10 20 \ 125 3 3 4 13 24 30 40 \ 126 4 3 4 40 60 30 40 \ 127 1 1 2 13 24 10 20 ] 128 129 execsql { 130 DELETE FROM rlog; 131 DELETE FROM tbl; 132 INSERT INTO tbl VALUES (100, 100); 133 INSERT INTO tbl VALUES (300, 200); 134 CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW 135 BEGIN 136 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 137 old.a, old.b, 138 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 139 0, 0); 140 END; 141 142 CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW 143 BEGIN 144 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 145 old.a, old.b, 146 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 147 0, 0); 148 END; 149 } 150 do_test trigger2-1.$ii.2 { 151 set r {} 152 foreach v [execsql { 153 DELETE FROM tbl; 154 SELECT * FROM rlog; 155 }] { 156 lappend r [expr {int($v)}] 157 } 158 set r 159 } [list 1 100 100 400 300 0 0 \ 160 2 100 100 300 200 0 0 \ 161 3 300 200 300 200 0 0 \ 162 4 300 200 0 0 0 0 ] 163 164 execsql { 165 DELETE FROM rlog; 166 CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW 167 BEGIN 168 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 169 0, 0, 170 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 171 new.a, new.b); 172 END; 173 174 CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW 175 BEGIN 176 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 177 0, 0, 178 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 179 new.a, new.b); 180 END; 181 } 182 do_test trigger2-1.$ii.3 { 183 execsql { 184 185 CREATE TABLE other_tbl(a, b); 186 INSERT INTO other_tbl VALUES(1, 2); 187 INSERT INTO other_tbl VALUES(3, 4); 188 -- INSERT INTO tbl SELECT * FROM other_tbl; 189 INSERT INTO tbl VALUES(5, 6); 190 DROP TABLE other_tbl; 191 192 SELECT * FROM rlog; 193 } 194 } [list 1 0 0 0.0 0.0 5 6 \ 195 2 0 0 5.0 6.0 5 6 ] 196 197 integrity_check trigger2-1.$ii.4 198 } 199 catchsql { 200 DROP TABLE rlog; 201 DROP TABLE clog; 202 DROP TABLE tbl; 203 DROP TABLE other_tbl; 204 } 205} 206 207# 2. 208set ii 0 209foreach tr_program { 210 {UPDATE tbl SET b = old.b;} 211 {INSERT INTO log VALUES(new.c, 2, 3);} 212 {DELETE FROM log WHERE a = 1;} 213 {INSERT INTO tbl VALUES(500, new.b * 10, 700); 214 UPDATE tbl SET c = old.c; 215 DELETE FROM log;} 216 {INSERT INTO log select * from tbl;} 217} { 218 foreach test_varset [ list \ 219 { 220 set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 221 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 222 set newC 10 223 set newB 2 224 set newA 1 225 set oldA 1 226 set oldB 2 227 set oldC 3 228 } \ 229 { 230 set statement {DELETE FROM tbl WHERE a = 1;} 231 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 232 set oldA 1 233 set oldB 2 234 set oldC 3 235 } \ 236 { 237 set statement {INSERT INTO tbl VALUES(1, 2, 3);} 238 set newA 1 239 set newB 2 240 set newC 3 241 } 242 ] \ 243 { 244 set statement {} 245 set prep {} 246 set newA {''} 247 set newB {''} 248 set newC {''} 249 set oldA {''} 250 set oldB {''} 251 set oldC {''} 252 253 incr ii 254 255 eval $test_varset 256 257 set statement_type [string range $statement 0 5] 258 set tr_program_fixed $tr_program 259 if {$statement_type == "DELETE"} { 260 regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 261 regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 262 regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 263 } 264 if {$statement_type == "INSERT"} { 265 regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 266 regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 267 regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 268 } 269 270 271 set tr_program_cooked $tr_program 272 regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 273 regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 274 regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 275 regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 276 regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 277 regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 278 279 catchsql { 280 DROP TABLE tbl; 281 DROP TABLE log; 282 } 283 284 execsql { 285 CREATE TABLE tbl(a PRIMARY KEY, b, c); 286 CREATE TABLE log(a, b, c); 287 } 288 289 set query {SELECT * FROM tbl; SELECT * FROM log;} 290 set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\ 291 INSERT INTO log VALUES(10, 20, 30);" 292 293# Check execution of BEFORE programs: 294 295 set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] 296 297 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 298 execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\ 299 ON tbl BEGIN $tr_program_fixed END;" 300 301 do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data 302 303 execsql "DROP TRIGGER the_trigger;" 304 execsql "DELETE FROM tbl; DELETE FROM log;" 305 306# Check execution of AFTER programs 307 set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] 308 309 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 310 execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\ 311 ON tbl BEGIN $tr_program_fixed END;" 312 313 do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data 314 execsql "DROP TRIGGER the_trigger;" 315 316 integrity_check trigger2-2.$ii-integrity 317 } 318} 319catchsql { 320 DROP TABLE tbl; 321 DROP TABLE log; 322} 323 324# 3. 325 326# trigger2-3.1: UPDATE OF triggers 327execsql { 328 CREATE TABLE tbl (a, b, c, d); 329 CREATE TABLE log (a); 330 INSERT INTO log VALUES (0); 331 INSERT INTO tbl VALUES (0, 0, 0, 0); 332 INSERT INTO tbl VALUES (1, 0, 0, 0); 333 CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl 334 BEGIN 335 UPDATE log SET a = a + 1; 336 END; 337} 338do_test trigger2-3.1 { 339 execsql { 340 UPDATE tbl SET b = 1, c = 10; -- 2 341 UPDATE tbl SET b = 10; -- 0 342 UPDATE tbl SET d = 4 WHERE a = 0; --1 343 UPDATE tbl SET a = 4, b = 10; --0 344 SELECT * FROM log; 345 } 346} {3} 347execsql { 348 DROP TABLE tbl; 349 DROP TABLE log; 350} 351 352# trigger2-3.2: WHEN clause 353set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}] 354ifcapable subquery { 355 lappend when_triggers \ 356 {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} 357} 358 359execsql { 360 CREATE TABLE tbl (a, b, c, d); 361 CREATE TABLE log (a); 362 INSERT INTO log VALUES (0); 363} 364 365foreach trig $when_triggers { 366 execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;" 367} 368 369ifcapable subquery { 370 set t232 {1 0 1} 371} else { 372 set t232 {0 0 1} 373} 374do_test trigger2-3.2 { 375 execsql { 376 377 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery) 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} $t232 390execsql { 391 DROP TABLE tbl; 392 DROP TABLE log; 393} 394integrity_check trigger2-3.3 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 579ifcapable view { 580 581do_test trigger2-7.1 { 582 execsql { 583 CREATE TABLE ab(a, b); 584 CREATE TABLE cd(c, d); 585 INSERT INTO ab VALUES (1, 2); 586 INSERT INTO ab VALUES (0, 0); 587 INSERT INTO cd VALUES (3, 4); 588 589 CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 590 olda, oldb, oldc, oldd, newa, newb, newc, newd); 591 592 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 593 594 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN 595 INSERT INTO tlog VALUES(NULL, 596 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 597 END; 598 CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN 599 INSERT INTO tlog VALUES(NULL, 600 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 601 END; 602 603 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN 604 INSERT INTO tlog VALUES(NULL, 605 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 606 END; 607 CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN 608 INSERT INTO tlog VALUES(NULL, 609 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 610 END; 611 612 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN 613 INSERT INTO tlog VALUES(NULL, 614 0, 0, 0, 0, new.a, new.b, new.c, new.d); 615 END; 616 CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN 617 INSERT INTO tlog VALUES(NULL, 618 0, 0, 0, 0, new.a, new.b, new.c, new.d); 619 END; 620 } 621} {}; 622 623do_test trigger2-7.2 { 624 execsql { 625 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 626 DELETE FROM abcd WHERE a = 1; 627 INSERT INTO abcd VALUES(10, 20, 30, 40); 628 SELECT * FROM tlog; 629 } 630} [ list 1 1 2 3 4 100 25 3 4 \ 631 2 1 2 3 4 100 25 3 4 \ 632 3 1 2 3 4 0 0 0 0 \ 633 4 1 2 3 4 0 0 0 0 \ 634 5 0 0 0 0 10 20 30 40 \ 635 6 0 0 0 0 10 20 30 40 ] 636 637do_test trigger2-7.3 { 638 execsql { 639 DELETE FROM tlog; 640 INSERT INTO abcd VALUES(10, 20, 30, 40); 641 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 642 DELETE FROM abcd WHERE a = 1; 643 SELECT * FROM tlog; 644 } 645} [ list \ 646 1 0 0 0 0 10 20 30 40 \ 647 2 0 0 0 0 10 20 30 40 \ 648 3 1 2 3 4 100 25 3 4 \ 649 4 1 2 3 4 100 25 3 4 \ 650 5 1 2 3 4 0 0 0 0 \ 651 6 1 2 3 4 0 0 0 0 \ 652] 653do_test trigger2-7.4 { 654 execsql { 655 DELETE FROM tlog; 656 DELETE FROM abcd WHERE a = 1; 657 INSERT INTO abcd VALUES(10, 20, 30, 40); 658 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 659 SELECT * FROM tlog; 660 } 661} [ list \ 662 1 1 2 3 4 0 0 0 0 \ 663 2 1 2 3 4 0 0 0 0 \ 664 3 0 0 0 0 10 20 30 40 \ 665 4 0 0 0 0 10 20 30 40 \ 666 5 1 2 3 4 100 25 3 4 \ 667 6 1 2 3 4 100 25 3 4 \ 668] 669 670do_test trigger2-8.1 { 671 execsql { 672 CREATE TABLE t1(a,b,c); 673 INSERT INTO t1 VALUES(1,2,3); 674 CREATE VIEW v1 AS 675 SELECT a+b AS x, b+c AS y, a+c AS z FROM t1; 676 SELECT * FROM v1; 677 } 678} {3 5 4} 679do_test trigger2-8.2 { 680 execsql { 681 CREATE TABLE v1log(a,b,c,d,e,f); 682 CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN 683 INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL); 684 END; 685 DELETE FROM v1 WHERE x=1; 686 SELECT * FROM v1log; 687 } 688} {} 689do_test trigger2-8.3 { 690 execsql { 691 DELETE FROM v1 WHERE x=3; 692 SELECT * FROM v1log; 693 } 694} {3 {} 5 {} 4 {}} 695do_test trigger2-8.4 { 696 execsql { 697 INSERT INTO t1 VALUES(4,5,6); 698 DELETE FROM v1log; 699 DELETE FROM v1 WHERE y=11; 700 SELECT * FROM v1log; 701 } 702} {9 {} 11 {} 10 {}} 703do_test trigger2-8.5 { 704 execsql { 705 CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN 706 INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z); 707 END; 708 DELETE FROM v1log; 709 INSERT INTO v1 VALUES(1,2,3); 710 SELECT * FROM v1log; 711 } 712} {{} 1 {} 2 {} 3} 713do_test trigger2-8.6 { 714 execsql { 715 CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN 716 INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z); 717 END; 718 DELETE FROM v1log; 719 UPDATE v1 SET x=x+100, y=y+200, z=z+300; 720 SELECT * FROM v1log; 721 } 722} {3 103 5 205 4 304 9 109 11 211 10 310} 723 724} ;# ifcapable view 725 726integrity_check trigger2-9.9 727 728finish_test 729