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