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 269#----------------------------------------------------------------------- 270# This next block of tests, triggerC-4.*, checks that affinity 271# transformations and constraint processing is performed at the correct 272# times relative to BEFORE and AFTER triggers. 273# 274# For an INSERT statement, for each row to be inserted: 275# 276# 1. Apply affinities to non-rowid values to be inserted. 277# 2. Fire BEFORE triggers. 278# 3. Process constraints. 279# 4. Insert new record. 280# 5. Fire AFTER triggers. 281# 282# If the value of the rowid field is to be automatically assigned, it is 283# set to -1 in the new.* record. Even if it is explicitly set to NULL 284# by the INSERT statement. 285# 286# For an UPDATE statement, for each row to be deleted: 287# 288# 1. Apply affinities to non-rowid values to be inserted. 289# 2. Fire BEFORE triggers. 290# 3. Process constraints. 291# 4. Insert new record. 292# 5. Fire AFTER triggers. 293# 294# For a DELETE statement, for each row to be deleted: 295# 296# 1. Fire BEFORE triggers. 297# 2. Remove database record. 298# 3. Fire AFTER triggers. 299# 300# When a numeric value that as an exact integer representation is stored 301# in a column with REAL affinity, it is actually stored as an integer. 302# These tests check that the typeof() such values is always 'real', 303# not 'integer'. 304# 305# triggerC-4.1.*: Check that affinity transformations are made before 306# triggers are invoked. 307# 308do_test triggerC-4.1.1 { 309 catchsql { DROP TABLE log } 310 catchsql { DROP TABLE t4 } 311 execsql { 312 CREATE TABLE log(t); 313 CREATE TABLE t4(a TEXT,b INTEGER,c REAL); 314 CREATE TRIGGER t4bi BEFORE INSERT ON t4 BEGIN 315 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 316 new.a || ' ' || typeof(new.a) || ' ' || 317 new.b || ' ' || typeof(new.b) || ' ' || 318 new.c || ' ' || typeof(new.c) 319 ); 320 END; 321 CREATE TRIGGER t4ai AFTER INSERT ON t4 BEGIN 322 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 323 new.a || ' ' || typeof(new.a) || ' ' || 324 new.b || ' ' || typeof(new.b) || ' ' || 325 new.c || ' ' || typeof(new.c) 326 ); 327 END; 328 CREATE TRIGGER t4bd BEFORE DELETE ON t4 BEGIN 329 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 330 old.a || ' ' || typeof(old.a) || ' ' || 331 old.b || ' ' || typeof(old.b) || ' ' || 332 old.c || ' ' || typeof(old.c) 333 ); 334 END; 335 CREATE TRIGGER t4ad AFTER DELETE ON t4 BEGIN 336 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 337 old.a || ' ' || typeof(old.a) || ' ' || 338 old.b || ' ' || typeof(old.b) || ' ' || 339 old.c || ' ' || typeof(old.c) 340 ); 341 END; 342 CREATE TRIGGER t4bu BEFORE UPDATE ON t4 BEGIN 343 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 344 old.a || ' ' || typeof(old.a) || ' ' || 345 old.b || ' ' || typeof(old.b) || ' ' || 346 old.c || ' ' || typeof(old.c) 347 ); 348 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 349 new.a || ' ' || typeof(new.a) || ' ' || 350 new.b || ' ' || typeof(new.b) || ' ' || 351 new.c || ' ' || typeof(new.c) 352 ); 353 END; 354 CREATE TRIGGER t4au AFTER UPDATE ON t4 BEGIN 355 INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || 356 old.a || ' ' || typeof(old.a) || ' ' || 357 old.b || ' ' || typeof(old.b) || ' ' || 358 old.c || ' ' || typeof(old.c) 359 ); 360 INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || 361 new.a || ' ' || typeof(new.a) || ' ' || 362 new.b || ' ' || typeof(new.b) || ' ' || 363 new.c || ' ' || typeof(new.c) 364 ); 365 END; 366 } 367} {} 368foreach {n insert log} { 369 370 2 { 371 INSERT INTO t4 VALUES('1', '1', '1'); 372 DELETE FROM t4; 373 } { 374 -1 integer 1 text 1 integer 1.0 real 375 1 integer 1 text 1 integer 1.0 real 376 1 integer 1 text 1 integer 1.0 real 377 1 integer 1 text 1 integer 1.0 real 378 } 379 380 3 { 381 INSERT INTO t4(rowid,a,b,c) VALUES(45, 45, 45, 45); 382 DELETE FROM t4; 383 } { 384 45 integer 45 text 45 integer 45.0 real 385 45 integer 45 text 45 integer 45.0 real 386 45 integer 45 text 45 integer 45.0 real 387 45 integer 45 text 45 integer 45.0 real 388 } 389 390 4 { 391 INSERT INTO t4(rowid,a,b,c) VALUES(-42.0, -42.0, -42.0, -42.0); 392 DELETE FROM t4; 393 } { 394 -42 integer -42.0 text -42 integer -42.0 real 395 -42 integer -42.0 text -42 integer -42.0 real 396 -42 integer -42.0 text -42 integer -42.0 real 397 -42 integer -42.0 text -42 integer -42.0 real 398 } 399 400 5 { 401 INSERT INTO t4(rowid,a,b,c) VALUES(NULL, -42.4, -42.4, -42.4); 402 DELETE FROM t4; 403 } { 404 -1 integer -42.4 text -42.4 real -42.4 real 405 1 integer -42.4 text -42.4 real -42.4 real 406 1 integer -42.4 text -42.4 real -42.4 real 407 1 integer -42.4 text -42.4 real -42.4 real 408 } 409 410 6 { 411 INSERT INTO t4 VALUES(7, 7, 7); 412 UPDATE t4 SET a=8, b=8, c=8; 413 } { 414 -1 integer 7 text 7 integer 7.0 real 415 1 integer 7 text 7 integer 7.0 real 416 1 integer 7 text 7 integer 7.0 real 417 1 integer 8 text 8 integer 8.0 real 418 1 integer 7 text 7 integer 7.0 real 419 1 integer 8 text 8 integer 8.0 real 420 } 421 422 7 { 423 UPDATE t4 SET rowid=2; 424 } { 425 1 integer 8 text 8 integer 8.0 real 426 2 integer 8 text 8 integer 8.0 real 427 1 integer 8 text 8 integer 8.0 real 428 2 integer 8 text 8 integer 8.0 real 429 } 430 431 8 { 432 UPDATE t4 SET a='9', b='9', c='9'; 433 } { 434 2 integer 8 text 8 integer 8.0 real 435 2 integer 9 text 9 integer 9.0 real 436 2 integer 8 text 8 integer 8.0 real 437 2 integer 9 text 9 integer 9.0 real 438 } 439 440 9 { 441 UPDATE t4 SET a='9.1', b='9.1', c='9.1'; 442 } { 443 2 integer 9 text 9 integer 9.0 real 444 2 integer 9.1 text 9.1 real 9.1 real 445 2 integer 9 text 9 integer 9.0 real 446 2 integer 9.1 text 9.1 real 9.1 real 447 } 448} { 449 do_test triggerC-4.1.$n { 450 eval concat [execsql " 451 DELETE FROM log; 452 $insert ; 453 SELECT * FROM log; 454 "] 455 } [join $log " "] 456} 457 458finish_test 459 460 461