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