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 476# Handling of ON CONFLICT by INSERT statements inside triggers 477execsql { 478 CREATE TABLE tbl (a primary key, b, c); 479 CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN 480 INSERT OR IGNORE INTO tbl values (new.a, 0, 0); 481 END; 482} 483do_test trigger2-6.1a { 484 execsql { 485 BEGIN; 486 INSERT INTO tbl values (1, 2, 3); 487 SELECT * from tbl; 488 } 489} {1 2 3} 490do_test trigger2-6.1b { 491 catchsql { 492 INSERT OR ABORT INTO tbl values (2, 2, 3); 493 } 494} {1 {column a is not unique}} 495do_test trigger2-6.1c { 496 execsql { 497 SELECT * from tbl; 498 } 499} {1 2 3} 500do_test trigger2-6.1d { 501 catchsql { 502 INSERT OR FAIL INTO tbl values (2, 2, 3); 503 } 504} {1 {column a is not unique}} 505do_test trigger2-6.1e { 506 execsql { 507 SELECT * from tbl; 508 } 509} {1 2 3 2 2 3} 510do_test trigger2-6.1f { 511 execsql { 512 INSERT OR REPLACE INTO tbl values (2, 2, 3); 513 SELECT * from tbl; 514 } 515} {1 2 3 2 0 0} 516do_test trigger2-6.1g { 517 catchsql { 518 INSERT OR ROLLBACK INTO tbl values (3, 2, 3); 519 } 520} {1 {column a is not unique}} 521do_test trigger2-6.1h { 522 execsql { 523 SELECT * from tbl; 524 } 525} {} 526execsql {DELETE FROM tbl} 527 528 529# Handling of ON CONFLICT by UPDATE statements inside triggers 530execsql { 531 INSERT INTO tbl values (4, 2, 3); 532 INSERT INTO tbl values (6, 3, 4); 533 CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN 534 UPDATE OR IGNORE tbl SET a = new.a, c = 10; 535 END; 536} 537do_test trigger2-6.2a { 538 execsql { 539 BEGIN; 540 UPDATE tbl SET a = 1 WHERE a = 4; 541 SELECT * from tbl; 542 } 543} {1 2 10 6 3 4} 544do_test trigger2-6.2b { 545 catchsql { 546 UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; 547 } 548} {1 {column a is not unique}} 549do_test trigger2-6.2c { 550 execsql { 551 SELECT * from tbl; 552 } 553} {1 2 10 6 3 4} 554do_test trigger2-6.2d { 555 catchsql { 556 UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; 557 } 558} {1 {column a is not unique}} 559do_test trigger2-6.2e { 560 execsql { 561 SELECT * from tbl; 562 } 563} {4 2 10 6 3 4} 564do_test trigger2-6.2f.1 { 565 execsql { 566 UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; 567 SELECT * from tbl; 568 } 569} {1 3 10} 570do_test trigger2-6.2f.2 { 571 execsql { 572 INSERT INTO tbl VALUES (2, 3, 4); 573 SELECT * FROM tbl; 574 } 575} {1 3 10 2 3 4} 576do_test trigger2-6.2g { 577 catchsql { 578 UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; 579 } 580} {1 {column a is not unique}} 581do_test trigger2-6.2h { 582 execsql { 583 SELECT * from tbl; 584 } 585} {4 2 3 6 3 4} 586execsql { 587 DROP TABLE tbl; 588} 589 590# 7. Triggers on views 591ifcapable view { 592 593do_test trigger2-7.1 { 594 execsql { 595 CREATE TABLE ab(a, b); 596 CREATE TABLE cd(c, d); 597 INSERT INTO ab VALUES (1, 2); 598 INSERT INTO ab VALUES (0, 0); 599 INSERT INTO cd VALUES (3, 4); 600 601 CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 602 olda, oldb, oldc, oldd, newa, newb, newc, newd); 603 604 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 605 606 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN 607 INSERT INTO tlog VALUES(NULL, 608 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 609 END; 610 CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN 611 INSERT INTO tlog VALUES(NULL, 612 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 613 END; 614 615 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN 616 INSERT INTO tlog VALUES(NULL, 617 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 618 END; 619 CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN 620 INSERT INTO tlog VALUES(NULL, 621 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 622 END; 623 624 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN 625 INSERT INTO tlog VALUES(NULL, 626 0, 0, 0, 0, new.a, new.b, new.c, new.d); 627 END; 628 CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN 629 INSERT INTO tlog VALUES(NULL, 630 0, 0, 0, 0, new.a, new.b, new.c, new.d); 631 END; 632 } 633} {}; 634 635do_test trigger2-7.2 { 636 execsql { 637 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 638 DELETE FROM abcd WHERE a = 1; 639 INSERT INTO abcd VALUES(10, 20, 30, 40); 640 SELECT * FROM tlog; 641 } 642} [ list 1 1 2 3 4 100 25 3 4 \ 643 2 1 2 3 4 100 25 3 4 \ 644 3 1 2 3 4 0 0 0 0 \ 645 4 1 2 3 4 0 0 0 0 \ 646 5 0 0 0 0 10 20 30 40 \ 647 6 0 0 0 0 10 20 30 40 ] 648 649do_test trigger2-7.3 { 650 execsql { 651 DELETE FROM tlog; 652 INSERT INTO abcd VALUES(10, 20, 30, 40); 653 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 654 DELETE FROM abcd WHERE a = 1; 655 SELECT * FROM tlog; 656 } 657} [ list \ 658 1 0 0 0 0 10 20 30 40 \ 659 2 0 0 0 0 10 20 30 40 \ 660 3 1 2 3 4 100 25 3 4 \ 661 4 1 2 3 4 100 25 3 4 \ 662 5 1 2 3 4 0 0 0 0 \ 663 6 1 2 3 4 0 0 0 0 \ 664] 665do_test trigger2-7.4 { 666 execsql { 667 DELETE FROM tlog; 668 DELETE FROM abcd WHERE a = 1; 669 INSERT INTO abcd VALUES(10, 20, 30, 40); 670 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 671 SELECT * FROM tlog; 672 } 673} [ list \ 674 1 1 2 3 4 0 0 0 0 \ 675 2 1 2 3 4 0 0 0 0 \ 676 3 0 0 0 0 10 20 30 40 \ 677 4 0 0 0 0 10 20 30 40 \ 678 5 1 2 3 4 100 25 3 4 \ 679 6 1 2 3 4 100 25 3 4 \ 680] 681 682do_test trigger2-8.1 { 683 execsql { 684 CREATE TABLE t1(a,b,c); 685 INSERT INTO t1 VALUES(1,2,3); 686 CREATE VIEW v1 AS 687 SELECT a+b AS x, b+c AS y, a+c AS z FROM t1; 688 SELECT * FROM v1; 689 } 690} {3 5 4} 691do_test trigger2-8.2 { 692 execsql { 693 CREATE TABLE v1log(a,b,c,d,e,f); 694 CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN 695 INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL); 696 END; 697 DELETE FROM v1 WHERE x=1; 698 SELECT * FROM v1log; 699 } 700} {} 701do_test trigger2-8.3 { 702 execsql { 703 DELETE FROM v1 WHERE x=3; 704 SELECT * FROM v1log; 705 } 706} {3 {} 5 {} 4 {}} 707do_test trigger2-8.4 { 708 execsql { 709 INSERT INTO t1 VALUES(4,5,6); 710 DELETE FROM v1log; 711 DELETE FROM v1 WHERE y=11; 712 SELECT * FROM v1log; 713 } 714} {9 {} 11 {} 10 {}} 715do_test trigger2-8.5 { 716 execsql { 717 CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN 718 INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z); 719 END; 720 DELETE FROM v1log; 721 INSERT INTO v1 VALUES(1,2,3); 722 SELECT * FROM v1log; 723 } 724} {{} 1 {} 2 {} 3} 725do_test trigger2-8.6 { 726 execsql { 727 CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN 728 INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z); 729 END; 730 DELETE FROM v1log; 731 UPDATE v1 SET x=x+100, y=y+200, z=z+300; 732 SELECT * FROM v1log; 733 } 734} {3 103 5 205 4 304 9 109 11 211 10 310} 735 736} ;# ifcapable view 737 738integrity_check trigger2-9.9 739 740finish_test 741