test/triggerC.test

   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
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix triggerC
  16 ifcapable {!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 #
  44 execsql { 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 #
  55 do_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 } {}
  80 do_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}
  86 do_test triggerC-1.3 {
  87   execsql { SELECT * FROM t1 }
  88 } {A B C}
  89 do_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}
  96 do_test triggerC-1.5 {
  97   execsql { SELECT * FROM t1 }
  98 } {a B C}
  99 do_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 {} {} {}}
 106 do_test triggerC-1.7 {
 107   execsql { SELECT * FROM t1 }
 108 } {}
 109 do_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 } {}
 117 do_test triggerC-1.9 {
 118   execsql { INSERT INTO t4 VALUES(1, 2) }
 119   catchsql { DELETE FROM t4 }
 120 } {1 {delete is not supported}}
 121 do_test triggerC-1.10 {
 122   execsql { SELECT * FROM t4 }
 123 } {1 2}
 124 do_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 } {}
 133 do_test triggerC-1.12 {
 134   catchsql { UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 }
 135 } {1 {too many levels of trigger recursion}}
 136 do_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 } {}
 146 do_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 } {}
 159 do_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 #
 169 do_test triggerC-2.1.0 {
 170   execsql {
 171     CREATE TABLE t2(a PRIMARY KEY);
 172   }
 173 } {}
 174
 175 foreach {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
 231 do_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
 249 do_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 #
 273 do_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 } {}
 284 do_test triggerC-3.1.2 {
 285   catchsql { INSERT INTO t3 VALUES(0,0) }
 286 } {1 {too many levels of trigger recursion}}
 287 do_test triggerC-3.1.3 {
 288   execsql { SELECT * FROM t3 }
 289 } {}
 290
 291 do_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}}
 302 do_test triggerC-3.2.2 {
 303   db eval {SELECT * FROM t3b}
 304 } {}
 305
 306 do_test triggerC-3.3.1 {
 307   catchsql "
 308     INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH + 1]);
 309   "
 310 } {0 {}}
 311 do_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
 315 do_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}}
 321 do_test triggerC-3.4.2 {
 322   db eval {SELECT count(*), max(x), min(x) FROM t3b}
 323 } {0 {} {}}
 324
 325 do_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 {}}
 331 do_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
 335 do_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}}
 341 do_test triggerC-3.5.4 {
 342   db eval {SELECT count(*), max(x), min(x) FROM t3b}
 343 } {0 {} {}}
 344
 345 do_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 {}}
 351 do_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
 355 do_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}}
 361 do_test triggerC-3.6.4 {
 362   db eval {SELECT count(*), max(x), min(x) FROM t3b}
 363 } {0 {} {}}
 364 sqlite3_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 #
 406 do_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 } {}
 466 foreach {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 #
 560 do_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 } {}
 575 foreach {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 }
 594 do_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 } {}
 602 foreach {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 }
 621 do_test triggerC-5.3.0 {
 622   execsql { PRAGMA recursive_triggers = off }
 623 } {}
 624 foreach {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 }
 643 do_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 #
 651 do_test triggerC-6.1 {
 652   execsql { PRAGMA recursive_triggers }
 653 } {1}
 654 do_test triggerC-6.2 {
 655   execsql {
 656     PRAGMA recursive_triggers = off;
 657     PRAGMA recursive_triggers;
 658   }
 659 } {0}
 660 do_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 #
 672 do_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 } {}
 687 do_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}}
 696 do_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
 706 do_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 } {}
 715 do_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}}
 724 do_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
 734 do_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 } {}
 746 do_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}}
 755 do_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 #
 767 do_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}
 779 do_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 #
 802 do_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
 821 do_test triggerC-10.2 {
 822   execsql {
 823     UPDATE t10 SET a = 'tcl', updatecnt = 5;
 824     SELECT * FROM t10;
 825   }
 826 } {tcl 5}
 827
 828 do_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 #
 863 do_test triggerC-11.0 {
 864   catchsql { DROP TABLE log }
 865   execsql  { CREATE TABLE log(a, b) }
 866 } {}
 867
 868 foreach {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 }
 906 do_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
 920 do_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}
 934 do_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
 941 do_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 } {}
 949 do_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 #
 964 set 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
 991 reset_db
 992 do_execsql_test triggerC-14.1 $SQL {1 2 3}
 993 reset_db
 994 optimization_control db factor-constants 0
 995 do_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 #
1001 do_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 }
1015 do_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
1023 do_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
1042 do_execsql_test 15.2.2 { SELECT * FROM x2;       } {1 2 3 4}
1043 do_execsql_test 15.2.3 { SELECT * FROM """x2"""; } {3 11 x y}
1044
1045 finish_test