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 52ifcapable {!trigger} { 53 finish_test 54 return 55} 56 57# 1. 58set ii 0 59foreach tbl_defn { 60 {CREATE TEMP TABLE tbl (a, b);} 61 {CREATE TABLE tbl (a, b);} 62 {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} 63 {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);} 64 {CREATE TABLE tbl (a, b PRIMARY KEY);} 65 {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 66 {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 67} { 68 incr ii 69 catchsql { DROP INDEX tbl_idx; } 70 catchsql { 71 DROP TABLE rlog; 72 DROP TABLE clog; 73 DROP TABLE tbl; 74 DROP TABLE other_tbl; 75 } 76 77 execsql $tbl_defn 78 79 execsql { 80 INSERT INTO tbl VALUES(1, 2); 81 INSERT INTO tbl VALUES(3, 4); 82 83 CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 84 CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 85 86 CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 87 BEGIN 88 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 89 old.a, old.b, 90 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 91 new.a, new.b); 92 END; 93 94 CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 95 BEGIN 96 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 97 old.a, old.b, 98 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 99 new.a, new.b); 100 END; 101 102 CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW 103 WHEN old.a = 1 104 BEGIN 105 INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 106 old.a, old.b, 107 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 108 new.a, new.b); 109 END; 110 } 111 112 do_test trigger2-1.$ii.1 { 113 set r {} 114 foreach v [execsql { 115 UPDATE tbl SET a = a * 10, b = b * 10; 116 SELECT * FROM rlog ORDER BY idx; 117 SELECT * FROM clog ORDER BY idx; 118 }] { 119 lappend r [expr {int($v)}] 120 } 121 set r 122 } [list 1 1 2 4 6 10 20 \ 123 2 1 2 13 24 10 20 \ 124 3 3 4 13 24 30 40 \ 125 4 3 4 40 60 30 40 \ 126 1 1 2 13 24 10 20 ] 127 128 execsql { 129 DELETE FROM rlog; 130 DELETE FROM tbl; 131 INSERT INTO tbl VALUES (100, 100); 132 INSERT INTO tbl VALUES (300, 200); 133 CREATE TRIGGER delete_before_row BEFORE 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 CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW 142 BEGIN 143 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 144 old.a, old.b, 145 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 146 0, 0); 147 END; 148 } 149 do_test trigger2-1.$ii.2 { 150 set r {} 151 foreach v [execsql { 152 DELETE FROM tbl; 153 SELECT * FROM rlog; 154 }] { 155 lappend r [expr {int($v)}] 156 } 157 set r 158 } [list 1 100 100 400 300 0 0 \ 159 2 100 100 300 200 0 0 \ 160 3 300 200 300 200 0 0 \ 161 4 300 200 0 0 0 0 ] 162 163 execsql { 164 DELETE FROM rlog; 165 CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW 166 BEGIN 167 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 168 0, 0, 169 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 170 new.a, new.b); 171 END; 172 173 CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW 174 BEGIN 175 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 176 0, 0, 177 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 178 new.a, new.b); 179 END; 180 } 181 do_test trigger2-1.$ii.3 { 182 execsql { 183 184 CREATE TABLE other_tbl(a, b); 185 INSERT INTO other_tbl VALUES(1, 2); 186 INSERT INTO other_tbl VALUES(3, 4); 187 -- INSERT INTO tbl SELECT * FROM other_tbl; 188 INSERT INTO tbl VALUES(5, 6); 189 DROP TABLE other_tbl; 190 191 SELECT * FROM rlog; 192 } 193 } [list 1 0 0 0.0 0.0 5 6 \ 194 2 0 0 5.0 6.0 5 6 ] 195 196 integrity_check trigger2-1.$ii.4 197} 198catchsql { 199 DROP TABLE rlog; 200 DROP TABLE clog; 201 DROP TABLE tbl; 202 DROP TABLE other_tbl; 203} 204 205# 2. 206set ii 0 207foreach tr_program { 208 {UPDATE tbl SET b = old.b;} 209 {INSERT INTO log VALUES(new.c, 2, 3);} 210 {DELETE FROM log WHERE a = 1;} 211 {INSERT INTO tbl VALUES(500, new.b * 10, 700); 212 UPDATE tbl SET c = old.c; 213 DELETE FROM log;} 214 {INSERT INTO log select * from tbl;} 215} { 216 foreach test_varset [ list \ 217 { 218 set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 219 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 220 set newC 10 221 set newB 2 222 set newA 1 223 set oldA 1 224 set oldB 2 225 set oldC 3 226 } \ 227 { 228 set statement {DELETE FROM tbl WHERE a = 1;} 229 set prep {INSERT INTO tbl VALUES(1, 2, 3);} 230 set oldA 1 231 set oldB 2 232 set oldC 3 233 } \ 234 { 235 set statement {INSERT INTO tbl VALUES(1, 2, 3);} 236 set newA 1 237 set newB 2 238 set newC 3 239 } 240 ] \ 241 { 242 set statement {} 243 set prep {} 244 set newA {''} 245 set newB {''} 246 set newC {''} 247 set oldA {''} 248 set oldB {''} 249 set oldC {''} 250 251 incr ii 252 253 eval $test_varset 254 255 set statement_type [string range $statement 0 5] 256 set tr_program_fixed $tr_program 257 if {$statement_type == "DELETE"} { 258 regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 259 regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 260 regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 261 } 262 if {$statement_type == "INSERT"} { 263 regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 264 regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 265 regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 266 } 267 268 269 set tr_program_cooked $tr_program 270 regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 271 regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 272 regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 273 regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 274 regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 275 regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 276 277 catchsql { 278 DROP TABLE tbl; 279 DROP TABLE log; 280 } 281 282 execsql { 283 CREATE TABLE tbl(a PRIMARY KEY, b, c); 284 CREATE TABLE log(a, b, c); 285 } 286 287 set query {SELECT * FROM tbl; SELECT * FROM log;} 288 set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\ 289 INSERT INTO log VALUES(10, 20, 30);" 290 291# Check execution of BEFORE programs: 292 293 set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] 294 295 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 296 execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\ 297 ON tbl BEGIN $tr_program_fixed END;" 298 299 do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data 300 301 execsql "DROP TRIGGER the_trigger;" 302 execsql "DELETE FROM tbl; DELETE FROM log;" 303 304# Check execution of AFTER programs 305 set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] 306 307 execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 308 execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\ 309 ON tbl BEGIN $tr_program_fixed END;" 310 311 do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data 312 execsql "DROP TRIGGER the_trigger;" 313 314 integrity_check trigger2-2.$ii-integrity 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} 385integrity_check trigger2-3.3 386 387# Simple cascaded trigger 388execsql { 389 CREATE TABLE tblA(a, b); 390 CREATE TABLE tblB(a, b); 391 CREATE TABLE tblC(a, b); 392 393 CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN 394 INSERT INTO tblB values(new.a, new.b); 395 END; 396 397 CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN 398 INSERT INTO tblC values(new.a, new.b); 399 END; 400} 401do_test trigger2-4.1 { 402 execsql { 403 INSERT INTO tblA values(1, 2); 404 SELECT * FROM tblA; 405 SELECT * FROM tblB; 406 SELECT * FROM tblC; 407 } 408} {1 2 1 2 1 2} 409execsql { 410 DROP TABLE tblA; 411 DROP TABLE tblB; 412 DROP TABLE tblC; 413} 414 415# Simple recursive trigger 416execsql { 417 CREATE TABLE tbl(a, b, c); 418 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 419 BEGIN 420 INSERT INTO tbl VALUES (new.a, new.b, new.c); 421 END; 422} 423do_test trigger2-4.2 { 424 execsql { 425 INSERT INTO tbl VALUES (1, 2, 3); 426 select * from tbl; 427 } 428} {1 2 3 1 2 3} 429execsql { 430 DROP TABLE tbl; 431} 432 433# 5. 434execsql { 435 CREATE TABLE tbl(a, b, c); 436 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 437 BEGIN 438 INSERT INTO tbl VALUES (1, 2, 3); 439 INSERT INTO tbl VALUES (2, 2, 3); 440 UPDATE tbl set b = 10 WHERE a = 1; 441 DELETE FROM tbl WHERE a = 1; 442 DELETE FROM tbl; 443 END; 444} 445do_test trigger2-5 { 446 execsql { 447 INSERT INTO tbl VALUES(100, 200, 300); 448 } 449 db changes 450} {1} 451execsql { 452 DROP TABLE tbl; 453} 454 455# Handling of ON CONFLICT by INSERT statements inside triggers 456execsql { 457 CREATE TABLE tbl (a primary key, b, c); 458 CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN 459 INSERT OR IGNORE INTO tbl values (new.a, 0, 0); 460 END; 461} 462do_test trigger2-6.1a { 463 execsql { 464 BEGIN; 465 INSERT INTO tbl values (1, 2, 3); 466 SELECT * from tbl; 467 } 468} {1 2 3} 469do_test trigger2-6.1b { 470 catchsql { 471 INSERT OR ABORT INTO tbl values (2, 2, 3); 472 } 473} {1 {column a is not unique}} 474do_test trigger2-6.1c { 475 execsql { 476 SELECT * from tbl; 477 } 478} {1 2 3} 479do_test trigger2-6.1d { 480 catchsql { 481 INSERT OR FAIL INTO tbl values (2, 2, 3); 482 } 483} {1 {column a is not unique}} 484do_test trigger2-6.1e { 485 execsql { 486 SELECT * from tbl; 487 } 488} {1 2 3 2 2 3} 489do_test trigger2-6.1f { 490 execsql { 491 INSERT OR REPLACE INTO tbl values (2, 2, 3); 492 SELECT * from tbl; 493 } 494} {1 2 3 2 0 0} 495do_test trigger2-6.1g { 496 catchsql { 497 INSERT OR ROLLBACK INTO tbl values (3, 2, 3); 498 } 499} {1 {column a is not unique}} 500do_test trigger2-6.1h { 501 execsql { 502 SELECT * from tbl; 503 } 504} {} 505execsql {DELETE FROM tbl} 506 507 508# Handling of ON CONFLICT by UPDATE statements inside triggers 509execsql { 510 INSERT INTO tbl values (4, 2, 3); 511 INSERT INTO tbl values (6, 3, 4); 512 CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN 513 UPDATE OR IGNORE tbl SET a = new.a, c = 10; 514 END; 515} 516do_test trigger2-6.2a { 517 execsql { 518 BEGIN; 519 UPDATE tbl SET a = 1 WHERE a = 4; 520 SELECT * from tbl; 521 } 522} {1 2 10 6 3 4} 523do_test trigger2-6.2b { 524 catchsql { 525 UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; 526 } 527} {1 {column a is not unique}} 528do_test trigger2-6.2c { 529 execsql { 530 SELECT * from tbl; 531 } 532} {1 2 10 6 3 4} 533do_test trigger2-6.2d { 534 catchsql { 535 UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; 536 } 537} {1 {column a is not unique}} 538do_test trigger2-6.2e { 539 execsql { 540 SELECT * from tbl; 541 } 542} {4 2 10 6 3 4} 543do_test trigger2-6.2f.1 { 544 execsql { 545 UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; 546 SELECT * from tbl; 547 } 548} {1 3 10} 549do_test trigger2-6.2f.2 { 550 execsql { 551 INSERT INTO tbl VALUES (2, 3, 4); 552 SELECT * FROM tbl; 553 } 554} {1 3 10 2 3 4} 555do_test trigger2-6.2g { 556 catchsql { 557 UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; 558 } 559} {1 {column a is not unique}} 560do_test trigger2-6.2h { 561 execsql { 562 SELECT * from tbl; 563 } 564} {4 2 3 6 3 4} 565execsql { 566 DROP TABLE tbl; 567} 568 569# 7. Triggers on views 570do_test trigger2-7.1 { 571 execsql { 572 CREATE TABLE ab(a, b); 573 CREATE TABLE cd(c, d); 574 INSERT INTO ab VALUES (1, 2); 575 INSERT INTO ab VALUES (0, 0); 576 INSERT INTO cd VALUES (3, 4); 577 578 CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 579 olda, oldb, oldc, oldd, newa, newb, newc, newd); 580 581 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 582 583 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN 584 INSERT INTO tlog VALUES(NULL, 585 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 586 END; 587 CREATE TRIGGER after_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 592 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN 593 INSERT INTO tlog VALUES(NULL, 594 old.a, old.b, old.c, old.d, 0, 0, 0, 0); 595 END; 596 CREATE TRIGGER after_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 601 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN 602 INSERT INTO tlog VALUES(NULL, 603 0, 0, 0, 0, new.a, new.b, new.c, new.d); 604 END; 605 CREATE TRIGGER after_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 } 610} {}; 611 612do_test trigger2-7.2 { 613 execsql { 614 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 615 DELETE FROM abcd WHERE a = 1; 616 INSERT INTO abcd VALUES(10, 20, 30, 40); 617 SELECT * FROM tlog; 618 } 619} [ list 1 1 2 3 4 100 25 3 4 \ 620 2 1 2 3 4 100 25 3 4 \ 621 3 1 2 3 4 0 0 0 0 \ 622 4 1 2 3 4 0 0 0 0 \ 623 5 0 0 0 0 10 20 30 40 \ 624 6 0 0 0 0 10 20 30 40 ] 625 626do_test trigger2-7.3 { 627 execsql { 628 DELETE FROM tlog; 629 INSERT INTO abcd VALUES(10, 20, 30, 40); 630 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 631 DELETE FROM abcd WHERE a = 1; 632 SELECT * FROM tlog; 633 } 634} [ list \ 635 1 0 0 0 0 10 20 30 40 \ 636 2 0 0 0 0 10 20 30 40 \ 637 3 1 2 3 4 100 25 3 4 \ 638 4 1 2 3 4 100 25 3 4 \ 639 5 1 2 3 4 0 0 0 0 \ 640 6 1 2 3 4 0 0 0 0 \ 641] 642do_test trigger2-7.4 { 643 execsql { 644 DELETE FROM tlog; 645 DELETE FROM abcd WHERE a = 1; 646 INSERT INTO abcd VALUES(10, 20, 30, 40); 647 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 648 SELECT * FROM tlog; 649 } 650} [ list \ 651 1 1 2 3 4 0 0 0 0 \ 652 2 1 2 3 4 0 0 0 0 \ 653 3 0 0 0 0 10 20 30 40 \ 654 4 0 0 0 0 10 20 30 40 \ 655 5 1 2 3 4 100 25 3 4 \ 656 6 1 2 3 4 100 25 3 4 \ 657] 658 659do_test trigger2-8.1 { 660 execsql { 661 CREATE TABLE t1(a,b,c); 662 INSERT INTO t1 VALUES(1,2,3); 663 CREATE VIEW v1 AS 664 SELECT a+b AS x, b+c AS y, a+c AS z FROM t1; 665 SELECT * FROM v1; 666 } 667} {3 5 4} 668do_test trigger2-8.2 { 669 execsql { 670 CREATE TABLE v1log(a,b,c,d,e,f); 671 CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN 672 INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL); 673 END; 674 DELETE FROM v1 WHERE x=1; 675 SELECT * FROM v1log; 676 } 677} {} 678do_test trigger2-8.3 { 679 execsql { 680 DELETE FROM v1 WHERE x=3; 681 SELECT * FROM v1log; 682 } 683} {3 {} 5 {} 4 {}} 684do_test trigger2-8.4 { 685 execsql { 686 INSERT INTO t1 VALUES(4,5,6); 687 DELETE FROM v1log; 688 DELETE FROM v1 WHERE y=11; 689 SELECT * FROM v1log; 690 } 691} {9 {} 11 {} 10 {}} 692do_test trigger2-8.5 { 693 execsql { 694 CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN 695 INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z); 696 END; 697 DELETE FROM v1log; 698 INSERT INTO v1 VALUES(1,2,3); 699 SELECT * FROM v1log; 700 } 701} {{} 1 {} 2 {} 3} 702do_test trigger2-8.6 { 703 execsql { 704 CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN 705 INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z); 706 END; 707 DELETE FROM v1log; 708 UPDATE v1 SET x=x+100, y=y+200, z=z+300; 709 SELECT * FROM v1log; 710 } 711} {3 103 5 205 4 304 9 109 11 211 10 310} 712 713integrity_check trigger2-9.9 714 715finish_test 716