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# Enable recursive triggers for this file. 21# 22execsql { PRAGMA recursive_triggers = on } 23 24#sqlite3_db_config_lookaside db 0 0 0 25 26#------------------------------------------------------------------------- 27# This block of tests, triggerC-1.*, are not aimed at any specific 28# property of the triggers sub-system. They were created to debug 29# specific problems while modifying SQLite to support recursive 30# triggers. They are left here in case they can help debug the 31# same problems again. 32# 33do_test triggerC-1.1 { 34 execsql { 35 CREATE TABLE t1(a, b, c); 36 CREATE TABLE log(t, a1, b1, c1, a2, b2, c2); 37 CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN 38 INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c); 39 END; 40 CREATE TRIGGER trig2 AFTER INSERT ON t1 BEGIN 41 INSERT INTO log VALUES('after', NULL, NULL, NULL, new.a, new.b, new.c); 42 END; 43 CREATE TRIGGER trig3 BEFORE UPDATE ON t1 BEGIN 44 INSERT INTO log VALUES('before', old.a,old.b,old.c, new.a,new.b,new.c); 45 END; 46 CREATE TRIGGER trig4 AFTER UPDATE ON t1 BEGIN 47 INSERT INTO log VALUES('after', old.a,old.b,old.c, new.a,new.b,new.c); 48 END; 49 50 CREATE TRIGGER trig5 BEFORE DELETE ON t1 BEGIN 51 INSERT INTO log VALUES('before', old.a,old.b,old.c, NULL,NULL,NULL); 52 END; 53 CREATE TRIGGER trig6 AFTER DELETE ON t1 BEGIN 54 INSERT INTO log VALUES('after', old.a,old.b,old.c, NULL,NULL,NULL); 55 END; 56 } 57} {} 58do_test triggerC-1.2 { 59 execsql { 60 INSERT INTO t1 VALUES('A', 'B', 'C'); 61 SELECT * FROM log; 62 } 63} {before {} {} {} A B C after {} {} {} A B C} 64do_test triggerC-1.3 { 65 execsql { SELECT * FROM t1 } 66} {A B C} 67do_test triggerC-1.4 { 68 execsql { 69 DELETE FROM log; 70 UPDATE t1 SET a = 'a'; 71 SELECT * FROM log; 72 } 73} {before A B C a B C after A B C a B C} 74do_test triggerC-1.5 { 75 execsql { SELECT * FROM t1 } 76} {a B C} 77do_test triggerC-1.6 { 78 execsql { 79 DELETE FROM log; 80 DELETE FROM t1; 81 SELECT * FROM log; 82 } 83} {before a B C {} {} {} after a B C {} {} {}} 84do_test triggerC-1.7 { 85 execsql { SELECT * FROM t1 } 86} {} 87do_test triggerC-1.8 { 88 execsql { 89 CREATE TABLE t4(a, b); 90 CREATE TRIGGER t4t AFTER DELETE ON t4 BEGIN 91 SELECT RAISE(ABORT, 'delete is not supported'); 92 END; 93 } 94} {} 95do_test triggerC-1.9 { 96 execsql { INSERT INTO t4 VALUES(1, 2) } 97 catchsql { DELETE FROM t4 } 98} {1 {delete is not supported}} 99do_test triggerC-1.10 { 100 execsql { SELECT * FROM t4 } 101} {1 2} 102do_test triggerC-1.11 { 103 execsql { 104 CREATE TABLE t5 (a primary key, b, c); 105 INSERT INTO t5 values (1, 2, 3); 106 CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN 107 UPDATE OR IGNORE t5 SET a = new.a, c = 10; 108 END; 109 } 110} {} 111do_test triggerC-1.12 { 112 catchsql { UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 } 113} {1 {too many levels of trigger recursion}} 114do_test triggerC-1.13 { 115 execsql { 116 CREATE TABLE t6(a INTEGER PRIMARY KEY, b); 117 INSERT INTO t6 VALUES(1, 2); 118 create trigger r1 after update on t6 for each row begin 119 SELECT 1; 120 end; 121 UPDATE t6 SET a=a; 122 } 123} {} 124do_test triggerC-1.14 { 125 execsql { 126 DROP TABLE t1; 127 CREATE TABLE cnt(n); 128 INSERT INTO cnt VALUES(0); 129 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c, d, e); 130 CREATE INDEX t1cd ON t1(c,d); 131 CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END; 132 INSERT INTO t1 VALUES(1,2,3,4,5); 133 INSERT INTO t1 VALUES(6,7,8,9,10); 134 INSERT INTO t1 VALUES(11,12,13,14,15); 135 } 136} {} 137do_test triggerC-1.15 { 138 catchsql { UPDATE OR ROLLBACK t1 SET a=100 } 139} {1 {PRIMARY KEY must be unique}} 140 141 142#------------------------------------------------------------------------- 143# This block of tests, triggerC-2.*, tests that recursive trigger 144# programs (triggers that fire themselves) work. More specifically, 145# this block focuses on recursive INSERT triggers. 146# 147do_test triggerC-2.1.0 { 148 execsql { 149 CREATE TABLE t2(a PRIMARY KEY); 150 } 151} {} 152 153foreach {n tdefn rc} { 154 1 { 155 CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN 156 INSERT INTO t2 VALUES(new.a - 1); 157 END; 158 } {0 {10 9 8 7 6 5 4 3 2 1 0}} 159 160 2 { 161 CREATE TRIGGER t2_trig AFTER INSERT ON t2 BEGIN 162 SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; 163 INSERT INTO t2 VALUES(new.a - 1); 164 END; 165 } {0 {10 9 8 7 6 5 4 3 2}} 166 167 3 { 168 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN 169 INSERT INTO t2 VALUES(new.a - 1); 170 END; 171 } {0 {0 1 2 3 4 5 6 7 8 9 10}} 172 173 4 { 174 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN 175 SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; 176 INSERT INTO t2 VALUES(new.a - 1); 177 END; 178 } {0 {3 4 5 6 7 8 9 10}} 179 180 5 { 181 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN 182 INSERT INTO t2 VALUES(new.a - 1); 183 END; 184 } {1 {too many levels of trigger recursion}} 185 186 6 { 187 CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN 188 INSERT OR IGNORE INTO t2 VALUES(new.a); 189 END; 190 } {0 10} 191 192 7 { 193 CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN 194 INSERT OR IGNORE INTO t2 VALUES(new.a); 195 END; 196 } {1 {too many levels of trigger recursion}} 197} { 198 do_test triggerC-2.1.$n { 199 catchsql { DROP TRIGGER t2_trig } 200 execsql { DELETE FROM t2 } 201 execsql $tdefn 202 catchsql { 203 INSERT INTO t2 VALUES(10); 204 SELECT * FROM t2; 205 } 206 } $rc 207} 208 209do_test triggerC-2.2 { 210 execsql { 211 CREATE TABLE t22(x); 212 213 CREATE TRIGGER t22a AFTER INSERT ON t22 BEGIN 214 INSERT INTO t22 SELECT x + (SELECT max(x) FROM t22) FROM t22; 215 END; 216 CREATE TRIGGER t22b BEFORE INSERT ON t22 BEGIN 217 SELECT CASE WHEN (SELECT count(*) FROM t22) >= 100 218 THEN RAISE(IGNORE) 219 ELSE NULL END; 220 END; 221 222 INSERT INTO t22 VALUES(1); 223 SELECT count(*) FROM t22; 224 } 225} {100} 226 227do_test triggerC-2.3 { 228 execsql { 229 CREATE TABLE t23(x PRIMARY KEY); 230 231 CREATE TRIGGER t23a AFTER INSERT ON t23 BEGIN 232 INSERT INTO t23 VALUES(new.x + 1); 233 END; 234 235 CREATE TRIGGER t23b BEFORE INSERT ON t23 BEGIN 236 SELECT CASE WHEN new.x>500 237 THEN RAISE(IGNORE) 238 ELSE NULL END; 239 END; 240 241 INSERT INTO t23 VALUES(1); 242 SELECT count(*) FROM t23; 243 } 244} {500} 245 246 247#----------------------------------------------------------------------- 248# This block of tests, triggerC-3.*, test that SQLite throws an exception 249# when it detects excessive recursion. 250# 251do_test triggerC-3.1.1 { 252 execsql { 253 CREATE TABLE t3(a, b); 254 CREATE TRIGGER t3i AFTER INSERT ON t3 BEGIN 255 DELETE FROM t3 WHERE rowid = new.rowid; 256 END; 257 CREATE TRIGGER t3d AFTER DELETE ON t3 BEGIN 258 INSERT INTO t3 VALUES(old.a, old.b); 259 END; 260 } 261} {} 262do_test triggerC-3.1.2 { 263 catchsql { INSERT INTO t3 VALUES(0,0) } 264} {1 {too many levels of trigger recursion}} 265do_test triggerC-3.1.3 { 266 execsql { SELECT * FROM t3 } 267} {} 268 269do_test triggerC-3.2.1 { 270 execsql { 271 CREATE TABLE t3b(x); 272 CREATE TRIGGER t3bi AFTER INSERT ON t3b WHEN new.x<2000 BEGIN 273 INSERT INTO t3b VALUES(new.x+1); 274 END; 275 } 276 catchsql { 277 INSERT INTO t3b VALUES(1); 278 } 279} {1 {too many levels of trigger recursion}} 280do_test triggerC-3.2.2 { 281 db eval {SELECT * FROM t3b} 282} {} 283 284do_test triggerC-3.3.1 { 285 catchsql { 286 INSERT INTO t3b VALUES(1000); 287 } 288} {0 {}} 289do_test triggerC-3.3.2 { 290 db eval {SELECT count(*), max(x), min(x) FROM t3b} 291} {1001 2000 1000} 292 293do_test triggerC-3.4.1 { 294 catchsql { 295 DELETE FROM t3b; 296 INSERT INTO t3b VALUES(999); 297 } 298} {1 {too many levels of trigger recursion}} 299do_test triggerC-3.4.2 { 300 db eval {SELECT count(*), max(x), min(x) FROM t3b} 301} {0 {} {}} 302 303do_test triggerC-3.5.1 { 304 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 100 305 catchsql { 306 INSERT INTO t3b VALUES(1900); 307 } 308} {0 {}} 309do_test triggerC-3.5.2 { 310 db eval {SELECT count(*), max(x), min(x) FROM t3b} 311} {101 2000 1900} 312 313do_test triggerC-3.5.3 { 314 catchsql { 315 DELETE FROM t3b; 316 INSERT INTO t3b VALUES(1899); 317 } 318} {1 {too many levels of trigger recursion}} 319do_test triggerC-3.5.4 { 320 db eval {SELECT count(*), max(x), min(x) FROM t3b} 321} {0 {} {}} 322 323do_test triggerC-3.6.1 { 324 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1 325 catchsql { 326 INSERT INTO t3b VALUES(1999); 327 } 328} {0 {}} 329do_test triggerC-3.6.2 { 330 db eval {SELECT count(*), max(x), min(x) FROM t3b} 331} {2 2000 1999} 332 333do_test triggerC-3.6.3 { 334 catchsql { 335 DELETE FROM t3b; 336 INSERT INTO t3b VALUES(1998); 337 } 338} {1 {too many levels of trigger recursion}} 339do_test triggerC-3.6.4 { 340 db eval {SELECT count(*), max(x), min(x) FROM t3b} 341} {0 {} {}} 342sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000 343 344 345#----------------------------------------------------------------------- 346# This next block of tests, triggerC-4.*, checks that affinity 347# transformations and constraint processing is performed at the correct 348# times relative to BEFORE and AFTER triggers. 349# 350# For an INSERT statement, for each row to be inserted: 351# 352# 1. Apply affinities to non-rowid values to be inserted. 353# 2. Fire BEFORE triggers. 354# 3. Process constraints. 355# 4. Insert new record. 356# 5. Fire AFTER triggers. 357# 358# If the value of the rowid field is to be automatically assigned, it is 359# set to -1 in the new.* record. Even if it is explicitly set to NULL 360# by the INSERT statement. 361# 362# For an UPDATE statement, for each row to be deleted: 363# 364# 1. Apply affinities to non-rowid values to be inserted. 365# 2. Fire BEFORE triggers. 366# 3. Process constraints. 367# 4. Insert new record. 368# 5. Fire AFTER triggers. 369# 370# For a DELETE statement, for each row to be deleted: 371# 372# 1. Fire BEFORE triggers. 373# 2. Remove database record. 374# 3. Fire AFTER triggers. 375# 376# When a numeric value that as an exact integer representation is stored 377# in a column with REAL affinity, it is actually stored as an integer. 378# These tests check that the typeof() such values is always 'real', 379# not 'integer'. 380# 381# triggerC-4.1.*: Check that affinity transformations are made before 382# triggers are invoked. 383# 384do_test triggerC-4.1.1 { 385 catchsql { DROP TABLE log } 386 catchsql { DROP TABLE t4 } 387 execsql { 388 CREATE TABLE log(t); 389 CREATE TABLE t4(a TEXT,b INTEGER,c REAL); 390 CREATE TRIGGER t4bi BEFORE INSERT ON t4 BEGIN 391 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 392 new.a || ' ' || typeof(new.a) || ' ' || 393 new.b || ' ' || typeof(new.b) || ' ' || 394 new.c || ' ' || typeof(new.c) 395 ); 396 END; 397 CREATE TRIGGER t4ai AFTER INSERT ON t4 BEGIN 398 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 399 new.a || ' ' || typeof(new.a) || ' ' || 400 new.b || ' ' || typeof(new.b) || ' ' || 401 new.c || ' ' || typeof(new.c) 402 ); 403 END; 404 CREATE TRIGGER t4bd BEFORE DELETE ON t4 BEGIN 405 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 406 old.a || ' ' || typeof(old.a) || ' ' || 407 old.b || ' ' || typeof(old.b) || ' ' || 408 old.c || ' ' || typeof(old.c) 409 ); 410 END; 411 CREATE TRIGGER t4ad AFTER DELETE ON t4 BEGIN 412 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 413 old.a || ' ' || typeof(old.a) || ' ' || 414 old.b || ' ' || typeof(old.b) || ' ' || 415 old.c || ' ' || typeof(old.c) 416 ); 417 END; 418 CREATE TRIGGER t4bu BEFORE UPDATE ON t4 BEGIN 419 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 420 old.a || ' ' || typeof(old.a) || ' ' || 421 old.b || ' ' || typeof(old.b) || ' ' || 422 old.c || ' ' || typeof(old.c) 423 ); 424 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 425 new.a || ' ' || typeof(new.a) || ' ' || 426 new.b || ' ' || typeof(new.b) || ' ' || 427 new.c || ' ' || typeof(new.c) 428 ); 429 END; 430 CREATE TRIGGER t4au AFTER UPDATE ON t4 BEGIN 431 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 432 old.a || ' ' || typeof(old.a) || ' ' || 433 old.b || ' ' || typeof(old.b) || ' ' || 434 old.c || ' ' || typeof(old.c) 435 ); 436 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 437 new.a || ' ' || typeof(new.a) || ' ' || 438 new.b || ' ' || typeof(new.b) || ' ' || 439 new.c || ' ' || typeof(new.c) 440 ); 441 END; 442 } 443} {} 444foreach {n insert log} { 445 446 2 { 447 INSERT INTO t4 VALUES('1', '1', '1'); 448 DELETE FROM t4; 449 } { 450 -1 integer 1 text 1 integer 1.0 real 451 1 integer 1 text 1 integer 1.0 real 452 1 integer 1 text 1 integer 1.0 real 453 1 integer 1 text 1 integer 1.0 real 454 } 455 456 3 { 457 INSERT INTO t4(rowid,a,b,c) VALUES(45, 45, 45, 45); 458 DELETE FROM t4; 459 } { 460 45 integer 45 text 45 integer 45.0 real 461 45 integer 45 text 45 integer 45.0 real 462 45 integer 45 text 45 integer 45.0 real 463 45 integer 45 text 45 integer 45.0 real 464 } 465 466 4 { 467 INSERT INTO t4(rowid,a,b,c) VALUES(-42.0, -42.0, -42.0, -42.0); 468 DELETE FROM t4; 469 } { 470 -42 integer -42.0 text -42 integer -42.0 real 471 -42 integer -42.0 text -42 integer -42.0 real 472 -42 integer -42.0 text -42 integer -42.0 real 473 -42 integer -42.0 text -42 integer -42.0 real 474 } 475 476 5 { 477 INSERT INTO t4(rowid,a,b,c) VALUES(NULL, -42.4, -42.4, -42.4); 478 DELETE FROM t4; 479 } { 480 -1 integer -42.4 text -42.4 real -42.4 real 481 1 integer -42.4 text -42.4 real -42.4 real 482 1 integer -42.4 text -42.4 real -42.4 real 483 1 integer -42.4 text -42.4 real -42.4 real 484 } 485 486 6 { 487 INSERT INTO t4 VALUES(7, 7, 7); 488 UPDATE t4 SET a=8, b=8, c=8; 489 } { 490 -1 integer 7 text 7 integer 7.0 real 491 1 integer 7 text 7 integer 7.0 real 492 1 integer 7 text 7 integer 7.0 real 493 1 integer 8 text 8 integer 8.0 real 494 1 integer 7 text 7 integer 7.0 real 495 1 integer 8 text 8 integer 8.0 real 496 } 497 498 7 { 499 UPDATE t4 SET rowid=2; 500 } { 501 1 integer 8 text 8 integer 8.0 real 502 2 integer 8 text 8 integer 8.0 real 503 1 integer 8 text 8 integer 8.0 real 504 2 integer 8 text 8 integer 8.0 real 505 } 506 507 8 { 508 UPDATE t4 SET a='9', b='9', c='9'; 509 } { 510 2 integer 8 text 8 integer 8.0 real 511 2 integer 9 text 9 integer 9.0 real 512 2 integer 8 text 8 integer 8.0 real 513 2 integer 9 text 9 integer 9.0 real 514 } 515 516 9 { 517 UPDATE t4 SET a='9.1', b='9.1', c='9.1'; 518 } { 519 2 integer 9 text 9 integer 9.0 real 520 2 integer 9.1 text 9.1 real 9.1 real 521 2 integer 9 text 9 integer 9.0 real 522 2 integer 9.1 text 9.1 real 9.1 real 523 } 524} { 525 do_test triggerC-4.1.$n { 526 eval concat [execsql " 527 DELETE FROM log; 528 $insert ; 529 SELECT * FROM log; 530 "] 531 } [join $log " "] 532} 533 534finish_test 535