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