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