1# 2009 August 24 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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15ifcapable {!trigger} { 16 finish_test 17 return 18} 19 20#------------------------------------------------------------------------- 21# Test organization: 22# 23# triggerC-1.*: Haphazardly designed trigger related tests that were useful 24# during an upgrade of the triggers sub-system. 25# 26# triggerC-2.*: 27# 28# triggerC-3.*: 29# 30# triggerC-4.*: 31# 32# triggerC-5.*: Test that when recursive triggers are enabled DELETE 33# triggers are fired when rows are deleted as part of OR 34# REPLACE conflict resolution. And that they are not fired 35# if recursive triggers are not enabled. 36# 37# triggerC-6.*: Test that the recursive_triggers pragma returns correct 38# results when invoked without an argument. 39# 40 41# Enable recursive triggers for this file. 42# 43execsql { PRAGMA recursive_triggers = on } 44 45#sqlite3_db_config_lookaside db 0 0 0 46 47#------------------------------------------------------------------------- 48# This block of tests, triggerC-1.*, are not aimed at any specific 49# property of the triggers sub-system. They were created to debug 50# specific problems while modifying SQLite to support recursive 51# triggers. They are left here in case they can help debug the 52# same problems again. 53# 54do_test triggerC-1.1 { 55 execsql { 56 CREATE TABLE t1(a, b, c); 57 CREATE TABLE log(t, a1, b1, c1, a2, b2, c2); 58 CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN 59 INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c); 60 END; 61 CREATE TRIGGER trig2 AFTER INSERT ON t1 BEGIN 62 INSERT INTO log VALUES('after', NULL, NULL, NULL, new.a, new.b, new.c); 63 END; 64 CREATE TRIGGER trig3 BEFORE UPDATE ON t1 BEGIN 65 INSERT INTO log VALUES('before', old.a,old.b,old.c, new.a,new.b,new.c); 66 END; 67 CREATE TRIGGER trig4 AFTER UPDATE ON t1 BEGIN 68 INSERT INTO log VALUES('after', old.a,old.b,old.c, new.a,new.b,new.c); 69 END; 70 71 CREATE TRIGGER trig5 BEFORE DELETE ON t1 BEGIN 72 INSERT INTO log VALUES('before', old.a,old.b,old.c, NULL,NULL,NULL); 73 END; 74 CREATE TRIGGER trig6 AFTER DELETE ON t1 BEGIN 75 INSERT INTO log VALUES('after', old.a,old.b,old.c, NULL,NULL,NULL); 76 END; 77 } 78} {} 79do_test triggerC-1.2 { 80 execsql { 81 INSERT INTO t1 VALUES('A', 'B', 'C'); 82 SELECT * FROM log; 83 } 84} {before {} {} {} A B C after {} {} {} A B C} 85do_test triggerC-1.3 { 86 execsql { SELECT * FROM t1 } 87} {A B C} 88do_test triggerC-1.4 { 89 execsql { 90 DELETE FROM log; 91 UPDATE t1 SET a = 'a'; 92 SELECT * FROM log; 93 } 94} {before A B C a B C after A B C a B C} 95do_test triggerC-1.5 { 96 execsql { SELECT * FROM t1 } 97} {a B C} 98do_test triggerC-1.6 { 99 execsql { 100 DELETE FROM log; 101 DELETE FROM t1; 102 SELECT * FROM log; 103 } 104} {before a B C {} {} {} after a B C {} {} {}} 105do_test triggerC-1.7 { 106 execsql { SELECT * FROM t1 } 107} {} 108do_test triggerC-1.8 { 109 execsql { 110 CREATE TABLE t4(a, b); 111 CREATE TRIGGER t4t AFTER DELETE ON t4 BEGIN 112 SELECT RAISE(ABORT, 'delete is not supported'); 113 END; 114 } 115} {} 116do_test triggerC-1.9 { 117 execsql { INSERT INTO t4 VALUES(1, 2) } 118 catchsql { DELETE FROM t4 } 119} {1 {delete is not supported}} 120do_test triggerC-1.10 { 121 execsql { SELECT * FROM t4 } 122} {1 2} 123do_test triggerC-1.11 { 124 execsql { 125 CREATE TABLE t5 (a primary key, b, c); 126 INSERT INTO t5 values (1, 2, 3); 127 CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN 128 UPDATE OR IGNORE t5 SET a = new.a, c = 10; 129 END; 130 } 131} {} 132do_test triggerC-1.12 { 133 catchsql { UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 } 134} {1 {too many levels of trigger recursion}} 135do_test triggerC-1.13 { 136 execsql { 137 CREATE TABLE t6(a INTEGER PRIMARY KEY, b); 138 INSERT INTO t6 VALUES(1, 2); 139 create trigger r1 after update on t6 for each row begin 140 SELECT 1; 141 end; 142 UPDATE t6 SET a=a; 143 } 144} {} 145do_test triggerC-1.14 { 146 execsql { 147 DROP TABLE t1; 148 CREATE TABLE cnt(n); 149 INSERT INTO cnt VALUES(0); 150 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c, d, e); 151 CREATE INDEX t1cd ON t1(c,d); 152 CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END; 153 INSERT INTO t1 VALUES(1,2,3,4,5); 154 INSERT INTO t1 VALUES(6,7,8,9,10); 155 INSERT INTO t1 VALUES(11,12,13,14,15); 156 } 157} {} 158do_test triggerC-1.15 { 159 catchsql { UPDATE OR ROLLBACK t1 SET a=100 } 160} {1 {PRIMARY KEY must be unique}} 161 162 163#------------------------------------------------------------------------- 164# This block of tests, triggerC-2.*, tests that recursive trigger 165# programs (triggers that fire themselves) work. More specifically, 166# this block focuses on recursive INSERT triggers. 167# 168do_test triggerC-2.1.0 { 169 execsql { 170 CREATE TABLE t2(a PRIMARY KEY); 171 } 172} {} 173 174foreach {n tdefn rc} { 175 1 { 176 CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN 177 INSERT INTO t2 VALUES(new.a - 1); 178 END; 179 } {0 {10 9 8 7 6 5 4 3 2 1 0}} 180 181 2 { 182 CREATE TRIGGER t2_trig AFTER INSERT ON t2 BEGIN 183 SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; 184 INSERT INTO t2 VALUES(new.a - 1); 185 END; 186 } {0 {10 9 8 7 6 5 4 3 2}} 187 188 3 { 189 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN 190 INSERT INTO t2 VALUES(new.a - 1); 191 END; 192 } {0 {0 1 2 3 4 5 6 7 8 9 10}} 193 194 4 { 195 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN 196 SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; 197 INSERT INTO t2 VALUES(new.a - 1); 198 END; 199 } {0 {3 4 5 6 7 8 9 10}} 200 201 5 { 202 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN 203 INSERT INTO t2 VALUES(new.a - 1); 204 END; 205 } {1 {too many levels of trigger recursion}} 206 207 6 { 208 CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN 209 INSERT OR IGNORE INTO t2 VALUES(new.a); 210 END; 211 } {0 10} 212 213 7 { 214 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN 215 INSERT OR IGNORE INTO t2 VALUES(new.a); 216 END; 217 } {1 {too many levels of trigger recursion}} 218} { 219 do_test triggerC-2.1.$n { 220 catchsql { DROP TRIGGER t2_trig } 221 execsql { DELETE FROM t2 } 222 execsql $tdefn 223 catchsql { 224 INSERT INTO t2 VALUES(10); 225 SELECT * FROM t2; 226 } 227 } $rc 228} 229 230do_test triggerC-2.2 { 231 execsql { 232 CREATE TABLE t22(x); 233 234 CREATE TRIGGER t22a AFTER INSERT ON t22 BEGIN 235 INSERT INTO t22 SELECT x + (SELECT max(x) FROM t22) FROM t22; 236 END; 237 CREATE TRIGGER t22b BEFORE INSERT ON t22 BEGIN 238 SELECT CASE WHEN (SELECT count(*) FROM t22) >= 100 239 THEN RAISE(IGNORE) 240 ELSE NULL END; 241 END; 242 243 INSERT INTO t22 VALUES(1); 244 SELECT count(*) FROM t22; 245 } 246} {100} 247 248do_test triggerC-2.3 { 249 execsql { 250 CREATE TABLE t23(x PRIMARY KEY); 251 252 CREATE TRIGGER t23a AFTER INSERT ON t23 BEGIN 253 INSERT INTO t23 VALUES(new.x + 1); 254 END; 255 256 CREATE TRIGGER t23b BEFORE INSERT ON t23 BEGIN 257 SELECT CASE WHEN new.x>500 258 THEN RAISE(IGNORE) 259 ELSE NULL END; 260 END; 261 262 INSERT INTO t23 VALUES(1); 263 SELECT count(*) FROM t23; 264 } 265} {500} 266 267 268#----------------------------------------------------------------------- 269# This block of tests, triggerC-3.*, test that SQLite throws an exception 270# when it detects excessive recursion. 271# 272do_test triggerC-3.1.1 { 273 execsql { 274 CREATE TABLE t3(a, b); 275 CREATE TRIGGER t3i AFTER INSERT ON t3 BEGIN 276 DELETE FROM t3 WHERE rowid = new.rowid; 277 END; 278 CREATE TRIGGER t3d AFTER DELETE ON t3 BEGIN 279 INSERT INTO t3 VALUES(old.a, old.b); 280 END; 281 } 282} {} 283do_test triggerC-3.1.2 { 284 catchsql { INSERT INTO t3 VALUES(0,0) } 285} {1 {too many levels of trigger recursion}} 286do_test triggerC-3.1.3 { 287 execsql { SELECT * FROM t3 } 288} {} 289 290do_test triggerC-3.2.1 { 291 execsql { 292 CREATE TABLE t3b(x); 293 CREATE TRIGGER t3bi AFTER INSERT ON t3b WHEN new.x<2000 BEGIN 294 INSERT INTO t3b VALUES(new.x+1); 295 END; 296 } 297 catchsql { 298 INSERT INTO t3b VALUES(1); 299 } 300} {1 {too many levels of trigger recursion}} 301do_test triggerC-3.2.2 { 302 db eval {SELECT * FROM t3b} 303} {} 304 305do_test triggerC-3.3.1 { 306 catchsql { 307 INSERT INTO t3b VALUES(1001); 308 } 309} {0 {}} 310do_test triggerC-3.3.2 { 311 db eval {SELECT count(*), max(x), min(x) FROM t3b} 312} {1000 2000 1001} 313 314do_test triggerC-3.4.1 { 315 catchsql { 316 DELETE FROM t3b; 317 INSERT INTO t3b VALUES(999); 318 } 319} {1 {too many levels of trigger recursion}} 320do_test triggerC-3.4.2 { 321 db eval {SELECT count(*), max(x), min(x) FROM t3b} 322} {0 {} {}} 323 324do_test triggerC-3.5.1 { 325 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 100 326 catchsql { 327 INSERT INTO t3b VALUES(1901); 328 } 329} {0 {}} 330do_test triggerC-3.5.2 { 331 db eval {SELECT count(*), max(x), min(x) FROM t3b} 332} {100 2000 1901} 333 334do_test triggerC-3.5.3 { 335 catchsql { 336 DELETE FROM t3b; 337 INSERT INTO t3b VALUES(1900); 338 } 339} {1 {too many levels of trigger recursion}} 340do_test triggerC-3.5.4 { 341 db eval {SELECT count(*), max(x), min(x) FROM t3b} 342} {0 {} {}} 343 344do_test triggerC-3.6.1 { 345 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1 346 catchsql { 347 INSERT INTO t3b VALUES(2000); 348 } 349} {0 {}} 350do_test triggerC-3.6.2 { 351 db eval {SELECT count(*), max(x), min(x) FROM t3b} 352} {1 2000 2000} 353 354do_test triggerC-3.6.3 { 355 catchsql { 356 DELETE FROM t3b; 357 INSERT INTO t3b VALUES(1999); 358 } 359} {1 {too many levels of trigger recursion}} 360do_test triggerC-3.6.4 { 361 db eval {SELECT count(*), max(x), min(x) FROM t3b} 362} {0 {} {}} 363sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000 364 365 366#----------------------------------------------------------------------- 367# This next block of tests, triggerC-4.*, checks that affinity 368# transformations and constraint processing is performed at the correct 369# times relative to BEFORE and AFTER triggers. 370# 371# For an INSERT statement, for each row to be inserted: 372# 373# 1. Apply affinities to non-rowid values to be inserted. 374# 2. Fire BEFORE triggers. 375# 3. Process constraints. 376# 4. Insert new record. 377# 5. Fire AFTER triggers. 378# 379# If the value of the rowid field is to be automatically assigned, it is 380# set to -1 in the new.* record. Even if it is explicitly set to NULL 381# by the INSERT statement. 382# 383# For an UPDATE statement, for each row to be deleted: 384# 385# 1. Apply affinities to non-rowid values to be inserted. 386# 2. Fire BEFORE triggers. 387# 3. Process constraints. 388# 4. Insert new record. 389# 5. Fire AFTER triggers. 390# 391# For a DELETE statement, for each row to be deleted: 392# 393# 1. Fire BEFORE triggers. 394# 2. Remove database record. 395# 3. Fire AFTER triggers. 396# 397# When a numeric value that as an exact integer representation is stored 398# in a column with REAL affinity, it is actually stored as an integer. 399# These tests check that the typeof() such values is always 'real', 400# not 'integer'. 401# 402# triggerC-4.1.*: Check that affinity transformations are made before 403# triggers are invoked. 404# 405do_test triggerC-4.1.1 { 406 catchsql { DROP TABLE log } 407 catchsql { DROP TABLE t4 } 408 execsql { 409 CREATE TABLE log(t); 410 CREATE TABLE t4(a TEXT,b INTEGER,c REAL); 411 CREATE TRIGGER t4bi BEFORE INSERT ON t4 BEGIN 412 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 413 new.a || ' ' || typeof(new.a) || ' ' || 414 new.b || ' ' || typeof(new.b) || ' ' || 415 new.c || ' ' || typeof(new.c) 416 ); 417 END; 418 CREATE TRIGGER t4ai AFTER INSERT ON t4 BEGIN 419 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 420 new.a || ' ' || typeof(new.a) || ' ' || 421 new.b || ' ' || typeof(new.b) || ' ' || 422 new.c || ' ' || typeof(new.c) 423 ); 424 END; 425 CREATE TRIGGER t4bd BEFORE DELETE ON t4 BEGIN 426 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 427 old.a || ' ' || typeof(old.a) || ' ' || 428 old.b || ' ' || typeof(old.b) || ' ' || 429 old.c || ' ' || typeof(old.c) 430 ); 431 END; 432 CREATE TRIGGER t4ad AFTER DELETE ON t4 BEGIN 433 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 434 old.a || ' ' || typeof(old.a) || ' ' || 435 old.b || ' ' || typeof(old.b) || ' ' || 436 old.c || ' ' || typeof(old.c) 437 ); 438 END; 439 CREATE TRIGGER t4bu BEFORE UPDATE ON t4 BEGIN 440 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 441 old.a || ' ' || typeof(old.a) || ' ' || 442 old.b || ' ' || typeof(old.b) || ' ' || 443 old.c || ' ' || typeof(old.c) 444 ); 445 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 446 new.a || ' ' || typeof(new.a) || ' ' || 447 new.b || ' ' || typeof(new.b) || ' ' || 448 new.c || ' ' || typeof(new.c) 449 ); 450 END; 451 CREATE TRIGGER t4au AFTER UPDATE ON t4 BEGIN 452 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 453 old.a || ' ' || typeof(old.a) || ' ' || 454 old.b || ' ' || typeof(old.b) || ' ' || 455 old.c || ' ' || typeof(old.c) 456 ); 457 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 458 new.a || ' ' || typeof(new.a) || ' ' || 459 new.b || ' ' || typeof(new.b) || ' ' || 460 new.c || ' ' || typeof(new.c) 461 ); 462 END; 463 } 464} {} 465foreach {n insert log} { 466 467 2 { 468 INSERT INTO t4 VALUES('1', '1', '1'); 469 DELETE FROM t4; 470 } { 471 -1 integer 1 text 1 integer 1.0 real 472 1 integer 1 text 1 integer 1.0 real 473 1 integer 1 text 1 integer 1.0 real 474 1 integer 1 text 1 integer 1.0 real 475 } 476 477 3 { 478 INSERT INTO t4(rowid,a,b,c) VALUES(45, 45, 45, 45); 479 DELETE FROM t4; 480 } { 481 45 integer 45 text 45 integer 45.0 real 482 45 integer 45 text 45 integer 45.0 real 483 45 integer 45 text 45 integer 45.0 real 484 45 integer 45 text 45 integer 45.0 real 485 } 486 487 4 { 488 INSERT INTO t4(rowid,a,b,c) VALUES(-42.0, -42.0, -42.0, -42.0); 489 DELETE FROM t4; 490 } { 491 -42 integer -42.0 text -42 integer -42.0 real 492 -42 integer -42.0 text -42 integer -42.0 real 493 -42 integer -42.0 text -42 integer -42.0 real 494 -42 integer -42.0 text -42 integer -42.0 real 495 } 496 497 5 { 498 INSERT INTO t4(rowid,a,b,c) VALUES(NULL, -42.4, -42.4, -42.4); 499 DELETE FROM t4; 500 } { 501 -1 integer -42.4 text -42.4 real -42.4 real 502 1 integer -42.4 text -42.4 real -42.4 real 503 1 integer -42.4 text -42.4 real -42.4 real 504 1 integer -42.4 text -42.4 real -42.4 real 505 } 506 507 6 { 508 INSERT INTO t4 VALUES(7, 7, 7); 509 UPDATE t4 SET a=8, b=8, c=8; 510 } { 511 -1 integer 7 text 7 integer 7.0 real 512 1 integer 7 text 7 integer 7.0 real 513 1 integer 7 text 7 integer 7.0 real 514 1 integer 8 text 8 integer 8.0 real 515 1 integer 7 text 7 integer 7.0 real 516 1 integer 8 text 8 integer 8.0 real 517 } 518 519 7 { 520 UPDATE t4 SET rowid=2; 521 } { 522 1 integer 8 text 8 integer 8.0 real 523 2 integer 8 text 8 integer 8.0 real 524 1 integer 8 text 8 integer 8.0 real 525 2 integer 8 text 8 integer 8.0 real 526 } 527 528 8 { 529 UPDATE t4 SET a='9', b='9', c='9'; 530 } { 531 2 integer 8 text 8 integer 8.0 real 532 2 integer 9 text 9 integer 9.0 real 533 2 integer 8 text 8 integer 8.0 real 534 2 integer 9 text 9 integer 9.0 real 535 } 536 537 9 { 538 UPDATE t4 SET a='9.1', b='9.1', c='9.1'; 539 } { 540 2 integer 9 text 9 integer 9.0 real 541 2 integer 9.1 text 9.1 real 9.1 real 542 2 integer 9 text 9 integer 9.0 real 543 2 integer 9.1 text 9.1 real 9.1 real 544 } 545} { 546 do_test triggerC-4.1.$n { 547 eval concat [execsql " 548 DELETE FROM log; 549 $insert ; 550 SELECT * FROM log; 551 "] 552 } [join $log " "] 553} 554 555#------------------------------------------------------------------------- 556# This block of tests, triggerC-5.*, test that DELETE triggers are fired 557# if a row is deleted as a result of OR REPLACE conflict resolution. 558# 559do_test triggerC-5.1.0 { 560 execsql { 561 DROP TABLE IF EXISTS t5; 562 CREATE TABLE t5(a INTEGER PRIMARY KEY, b); 563 CREATE UNIQUE INDEX t5i ON t5(b); 564 INSERT INTO t5 VALUES(1, 'a'); 565 INSERT INTO t5 VALUES(2, 'b'); 566 INSERT INTO t5 VALUES(3, 'c'); 567 568 CREATE TABLE t5g(a, b, c); 569 CREATE TRIGGER t5t BEFORE DELETE ON t5 BEGIN 570 INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); 571 END; 572 } 573} {} 574foreach {n dml t5g t5} { 575 1 "DELETE FROM t5 WHERE a=2" {2 b 3} {1 a 3 c} 576 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 3} {1 a 2 d 3 c} 577 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 3} {1 a 2 c} 578 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 3} {1 a 3 c 4 b} 579 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 3} {1 a 3 b} 580 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 3 3 c 2} {1 a 2 c} 581 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b} 582} { 583 do_test triggerC-5.1.$n { 584 execsql " 585 BEGIN; 586 $dml ; 587 SELECT * FROM t5g; 588 SELECT * FROM t5; 589 ROLLBACK; 590 " 591 } [concat $t5g $t5] 592} 593do_test triggerC-5.2.0 { 594 execsql { 595 DROP TRIGGER t5t; 596 CREATE TRIGGER t5t AFTER DELETE ON t5 BEGIN 597 INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); 598 END; 599 } 600} {} 601foreach {n dml t5g t5} { 602 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} 603 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 2} {1 a 2 d 3 c} 604 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 2} {1 a 2 c} 605 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 2} {1 a 3 c 4 b} 606 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 2} {1 a 3 b} 607 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 2 3 c 1} {1 a 2 c} 608 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b} 609} { 610 do_test triggerC-5.2.$n { 611 execsql " 612 BEGIN; 613 $dml ; 614 SELECT * FROM t5g; 615 SELECT * FROM t5; 616 ROLLBACK; 617 " 618 } [concat $t5g $t5] 619} 620do_test triggerC-5.3.0 { 621 execsql { PRAGMA recursive_triggers = off } 622} {} 623foreach {n dml t5g t5} { 624 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} 625 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {} {1 a 2 d 3 c} 626 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {} {1 a 2 c} 627 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {} {1 a 3 c 4 b} 628 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {} {1 a 3 b} 629 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {} {1 a 2 c} 630 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b} 631} { 632 do_test triggerC-5.3.$n { 633 execsql " 634 BEGIN; 635 $dml ; 636 SELECT * FROM t5g; 637 SELECT * FROM t5; 638 ROLLBACK; 639 " 640 } [concat $t5g $t5] 641} 642do_test triggerC-5.3.8 { 643 execsql { PRAGMA recursive_triggers = on } 644} {} 645 646#------------------------------------------------------------------------- 647# This block of tests, triggerC-6.*, tests that "PRAGMA recursive_triggers" 648# statements return the current value of the recursive triggers flag. 649# 650do_test triggerC-6.1 { 651 execsql { PRAGMA recursive_triggers } 652} {1} 653do_test triggerC-6.2 { 654 execsql { 655 PRAGMA recursive_triggers = off; 656 PRAGMA recursive_triggers; 657 } 658} {0} 659do_test triggerC-6.3 { 660 execsql { 661 PRAGMA recursive_triggers = on; 662 PRAGMA recursive_triggers; 663 } 664} {1} 665 666#------------------------------------------------------------------------- 667# Test some of the "undefined behaviour" associated with triggers. The 668# undefined behaviour occurs when a row being updated or deleted is 669# manipulated by a BEFORE trigger. 670# 671do_test triggerC-7.1 { 672 execsql { 673 CREATE TABLE t8(x); 674 CREATE TABLE t7(a, b); 675 INSERT INTO t7 VALUES(1, 2); 676 INSERT INTO t7 VALUES(3, 4); 677 INSERT INTO t7 VALUES(5, 6); 678 CREATE TRIGGER t7t BEFORE UPDATE ON t7 BEGIN 679 DELETE FROM t7 WHERE a = 1; 680 END; 681 CREATE TRIGGER t7ta AFTER UPDATE ON t7 BEGIN 682 INSERT INTO t8 VALUES('after fired ' || old.rowid || '->' || new.rowid); 683 END; 684 } 685} {} 686do_test triggerC-7.2 { 687 execsql { 688 BEGIN; 689 UPDATE t7 SET b=7 WHERE a = 5; 690 SELECT * FROM t7; 691 SELECT * FROM t8; 692 ROLLBACK; 693 } 694} {3 4 5 7 {after fired 3->3}} 695do_test triggerC-7.3 { 696 execsql { 697 BEGIN; 698 UPDATE t7 SET b=7 WHERE a = 1; 699 SELECT * FROM t7; 700 SELECT * FROM t8; 701 ROLLBACK; 702 } 703} {3 4 5 6} 704 705do_test triggerC-7.4 { 706 execsql { 707 DROP TRIGGER t7t; 708 CREATE TRIGGER t7t BEFORE UPDATE ON t7 WHEN (old.rowid!=1 OR new.rowid!=8) 709 BEGIN 710 UPDATE t7 set rowid = 8 WHERE rowid=1; 711 END; 712 } 713} {} 714do_test triggerC-7.5 { 715 execsql { 716 BEGIN; 717 UPDATE t7 SET b=7 WHERE a = 5; 718 SELECT rowid, * FROM t7; 719 SELECT * FROM t8; 720 ROLLBACK; 721 } 722} {2 3 4 3 5 7 8 1 2 {after fired 1->8} {after fired 3->3}} 723do_test triggerC-7.6 { 724 execsql { 725 BEGIN; 726 UPDATE t7 SET b=7 WHERE a = 1; 727 SELECT rowid, * FROM t7; 728 SELECT * FROM t8; 729 ROLLBACK; 730 } 731} {2 3 4 3 5 6 8 1 2 {after fired 1->8}} 732 733do_test triggerC-7.7 { 734 execsql { 735 DROP TRIGGER t7t; 736 DROP TRIGGER t7ta; 737 CREATE TRIGGER t7t BEFORE DELETE ON t7 BEGIN 738 UPDATE t7 set rowid = 8 WHERE rowid=1; 739 END; 740 CREATE TRIGGER t7ta AFTER DELETE ON t7 BEGIN 741 INSERT INTO t8 VALUES('after fired ' || old.rowid); 742 END; 743 } 744} {} 745do_test triggerC-7.8 { 746 execsql { 747 BEGIN; 748 DELETE FROM t7 WHERE a = 3; 749 SELECT rowid, * FROM t7; 750 SELECT * FROM t8; 751 ROLLBACK; 752 } 753} {3 5 6 8 1 2 {after fired 2}} 754do_test triggerC-7.9 { 755 execsql { 756 BEGIN; 757 DELETE FROM t7 WHERE a = 1; 758 SELECT rowid, * FROM t7; 759 SELECT * FROM t8; 760 ROLLBACK; 761 } 762} {2 3 4 3 5 6 8 1 2} 763 764# Ticket [e25d9ea771febc9c311928c1c01c3163dcb26643] 765# 766do_test triggerC-9.1 { 767 execsql { 768 CREATE TABLE t9(a,b); 769 CREATE INDEX t9b ON t9(b); 770 INSERT INTO t9 VALUES(1,0); 771 INSERT INTO t9 VALUES(2,1); 772 INSERT INTO t9 VALUES(3,2); 773 INSERT INTO t9 SELECT a+3, a+2 FROM t9; 774 INSERT INTO t9 SELECT a+6, a+5 FROM t9; 775 SELECT a FROM t9 ORDER BY a; 776 } 777} {1 2 3 4 5 6 7 8 9 10 11 12} 778do_test triggerC-9.2 { 779 execsql { 780 CREATE TRIGGER t9r1 AFTER DELETE ON t9 BEGIN 781 DELETE FROM t9 WHERE b=old.a; 782 END; 783 DELETE FROM t9 WHERE b=4; 784 SELECT a FROM t9 ORDER BY a; 785 } 786} {1 2 3 4} 787 788# At one point (between versions 3.6.18 and 3.6.20 inclusive), an UPDATE 789# that fired a BEFORE trigger that itself updated the same row as the 790# statement causing it to fire was causing a strange side-effect: The 791# values updated by the statement within the trigger were being overwritten 792# by the values in the new.* array, even if those values were not 793# themselves written by the parent UPDATE statement. 794# 795# Technically speaking this was not a bug. The SQLite documentation says 796# that if a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes the 797# row that the parent statement is operating on the results are undefined. 798# But as of 3.6.21 behaviour is restored to the way it was in versions 799# 3.6.17 and earlier to avoid causing unnecessary difficulties. 800# 801do_test triggerC-10.1 { 802 execsql { 803 CREATE TABLE t10(a, updatecnt DEFAULT 0); 804 CREATE TRIGGER t10_bu BEFORE UPDATE OF a ON t10 BEGIN 805 UPDATE t10 SET updatecnt = updatecnt+1 WHERE rowid = old.rowid; 806 END; 807 INSERT INTO t10(a) VALUES('hello'); 808 } 809 810 # Before the problem was fixed, table t10 would contain the tuple 811 # (world, 0) after running the following script (because the value 812 # 1 written to column "updatecnt" was clobbered by the old value 0). 813 # 814 execsql { 815 UPDATE t10 SET a = 'world'; 816 SELECT * FROM t10; 817 } 818} {world 1} 819 820do_test triggerC-10.2 { 821 execsql { 822 UPDATE t10 SET a = 'tcl', updatecnt = 5; 823 SELECT * FROM t10; 824 } 825} {tcl 5} 826 827do_test triggerC-10.3 { 828 execsql { 829 CREATE TABLE t11( 830 c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, 831 c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, 832 c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, 833 c31, c32, c33, c34, c35, c36, c37, c38, c39, c40 834 ); 835 836 CREATE TRIGGER t11_bu BEFORE UPDATE OF c1 ON t11 BEGIN 837 UPDATE t11 SET c31 = c31+1, c32=c32+1 WHERE rowid = old.rowid; 838 END; 839 840 INSERT INTO t11 VALUES( 841 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 842 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 843 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 844 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 845 ); 846 } 847 848 # Before the problem was fixed, table t10 would contain the tuple 849 # (world, 0) after running the following script (because the value 850 # 1 written to column "updatecnt" was clobbered by the old value 0). 851 # 852 execsql { 853 UPDATE t11 SET c4=35, c33=22, c1=5; 854 SELECT * FROM t11; 855 } 856} {5 2 3 35 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 32 33 22 34 35 36 37 38 39 40} 857 858#------------------------------------------------------------------------- 859# Test that bug [371bab5d65] has been fixed. BEFORE INSERT and INSTEAD OF 860# INSERT triggers with the DEFAULT VALUES INSERT syntax. 861# 862do_test triggerC-11.0 { 863 catchsql { DROP TABLE log } 864 execsql { CREATE TABLE log(a, b) } 865} {} 866 867foreach {testno tbl defaults} { 868 1 "CREATE TABLE t1(a, b)" {{} {}} 869 2 "CREATE TABLE t1(a DEFAULT 1, b DEFAULT 'abc')" {1 abc} 870 3 "CREATE TABLE t1(a, b DEFAULT 4.5)" {{} 4.5} 871} { 872 do_test triggerC-11.$testno.1 { 873 catchsql { DROP TABLE t1 } 874 execsql { DELETE FROM log } 875 execsql $tbl 876 execsql { 877 CREATE TRIGGER tt1 BEFORE INSERT ON t1 BEGIN 878 INSERT INTO log VALUES(new.a, new.b); 879 END; 880 INSERT INTO t1 DEFAULT VALUES; 881 SELECT * FROM log; 882 } 883 } $defaults 884 885 do_test triggerC-11.$testno.2 { 886 execsql { DELETE FROM log } 887 execsql { 888 CREATE TRIGGER tt2 AFTER INSERT ON t1 BEGIN 889 INSERT INTO log VALUES(new.a, new.b); 890 END; 891 INSERT INTO t1 DEFAULT VALUES; 892 SELECT * FROM log; 893 } 894 } [concat $defaults $defaults] 895 896 do_test triggerC-11.$testno.3 { 897 execsql { DROP TRIGGER tt1 } 898 execsql { DELETE FROM log } 899 execsql { 900 INSERT INTO t1 DEFAULT VALUES; 901 SELECT * FROM log; 902 } 903 } $defaults 904} 905do_test triggerC-11.4 { 906 catchsql { DROP TABLE t2 } 907 execsql { 908 DELETE FROM log; 909 CREATE TABLE t2(a, b); 910 CREATE VIEW v2 AS SELECT * FROM t2; 911 CREATE TRIGGER tv2 INSTEAD OF INSERT ON v2 BEGIN 912 INSERT INTO log VALUES(new.a, new.b); 913 END; 914 INSERT INTO v2 DEFAULT VALUES; 915 SELECT a, b, a IS NULL, b IS NULL FROM log; 916 } 917} {{} {} 1 1} 918 919do_test triggerC-12.1 { 920 db close 921 file delete -force test.db 922 sqlite3 db test.db 923 924 execsql { 925 CREATE TABLE t1(a, b); 926 INSERT INTO t1 VALUES(1, 2); 927 INSERT INTO t1 VALUES(3, 4); 928 INSERT INTO t1 VALUES(5, 6); 929 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1 ; END ; 930 SELECT count(*) FROM sqlite_master; 931 } 932} {2} 933do_test triggerC-12.2 { 934 db eval { SELECT * FROM t1 } { 935 if {$a == 3} { execsql { DROP TRIGGER tr1 } } 936 } 937 execsql { SELECT count(*) FROM sqlite_master } 938} {1} 939 940do_execsql_test triggerC-13.1 { 941 PRAGMA recursive_triggers = ON; 942 CREATE TABLE t12(a, b); 943 INSERT INTO t12 VALUES(1, 2); 944 CREATE TRIGGER tr12 AFTER UPDATE ON t12 BEGIN 945 UPDATE t12 SET a=new.a+1, b=new.b+1; 946 END; 947} {} 948do_catchsql_test triggerC-13.2 { 949 UPDATE t12 SET a=a+1, b=b+1; 950} {1 {too many levels of trigger recursion}} 951 952 953 954finish_test 955