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