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