Annotation of embedaddon/sqlite3/test/triggerC.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to triggerC.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test