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