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