Annotation of embedaddon/sqlite3/test/e_delete.test, revision 1.1
1.1 ! misho 1: # 2010 September 21
! 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: # This file implements tests to verify that the "testable statements" in
! 13: # the lang_delete.html document are correct.
! 14: #
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17:
! 18: proc do_delete_tests {args} {
! 19: uplevel do_select_tests $args
! 20: }
! 21:
! 22: do_execsql_test e_delete-0.0 {
! 23: CREATE TABLE t1(a, b);
! 24: CREATE INDEX i1 ON t1(a);
! 25: } {}
! 26:
! 27: # EVIDENCE-OF: R-62077-19799 -- syntax diagram delete-stmt
! 28: #
! 29: # EVIDENCE-OF: R-60796-31013 -- syntax diagram qualified-table-name
! 30: #
! 31: do_delete_tests e_delete-0.1 {
! 32: 1 "DELETE FROM t1" {}
! 33: 2 "DELETE FROM t1 INDEXED BY i1" {}
! 34: 3 "DELETE FROM t1 NOT INDEXED" {}
! 35: 4 "DELETE FROM main.t1" {}
! 36: 5 "DELETE FROM main.t1 INDEXED BY i1" {}
! 37: 6 "DELETE FROM main.t1 NOT INDEXED" {}
! 38: 7 "DELETE FROM t1 WHERE a>2" {}
! 39: 8 "DELETE FROM t1 INDEXED BY i1 WHERE a>2" {}
! 40: 9 "DELETE FROM t1 NOT INDEXED WHERE a>2" {}
! 41: 10 "DELETE FROM main.t1 WHERE a>2" {}
! 42: 11 "DELETE FROM main.t1 INDEXED BY i1 WHERE a>2" {}
! 43: 12 "DELETE FROM main.t1 NOT INDEXED WHERE a>2" {}
! 44: }
! 45:
! 46: # EVIDENCE-OF: R-20205-17349 If the WHERE clause is not present, all
! 47: # records in the table are deleted.
! 48: #
! 49: drop_all_tables
! 50: do_test e_delete-1.0 {
! 51: db transaction {
! 52: foreach t {t1 t2 t3 t4 t5 t6} {
! 53: execsql [string map [list %T% $t] {
! 54: CREATE TABLE %T%(x, y);
! 55: INSERT INTO %T% VALUES(1, 'one');
! 56: INSERT INTO %T% VALUES(2, 'two');
! 57: INSERT INTO %T% VALUES(3, 'three');
! 58: INSERT INTO %T% VALUES(4, 'four');
! 59: INSERT INTO %T% VALUES(5, 'five');
! 60: }]
! 61: }
! 62: }
! 63: } {}
! 64: do_delete_tests e_delete-1.1 {
! 65: 1 "DELETE FROM t1 ; SELECT * FROM t1" {}
! 66: 2 "DELETE FROM main.t2 ; SELECT * FROM t2" {}
! 67: }
! 68:
! 69: # EVIDENCE-OF: R-30203-16177 If a WHERE clause is supplied, then only
! 70: # those rows for which the result of evaluating the WHERE clause as a
! 71: # boolean expression is true are deleted.
! 72: #
! 73: do_delete_tests e_delete-1.2 {
! 74: 1 "DELETE FROM t3 WHERE 1 ; SELECT x FROM t3" {}
! 75: 2 "DELETE FROM main.t4 WHERE 0 ; SELECT x FROM t4" {1 2 3 4 5}
! 76: 3 "DELETE FROM t4 WHERE 0.0 ; SELECT x FROM t4" {1 2 3 4 5}
! 77: 4 "DELETE FROM t4 WHERE NULL ; SELECT x FROM t4" {1 2 3 4 5}
! 78: 5 "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4" {2}
! 79: 6 "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4" {}
! 80: 7 "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5" {1 2 3 4}
! 81: 8 "DELETE FROM t5 WHERE (SELECT max(x) FROM t4) ;SELECT x FROM t5" {1 2 3 4}
! 82: 9 "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) ;SELECT x FROM t5" {}
! 83: 10 "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6" {one four five}
! 84: }
! 85:
! 86:
! 87: #-------------------------------------------------------------------------
! 88: # Tests for restrictions on DELETE statements that appear within trigger
! 89: # programs.
! 90: #
! 91: forcedelete test.db2
! 92: forcedelete test.db3
! 93: do_execsql_test e_delete-2.0 {
! 94: ATTACH 'test.db2' AS aux;
! 95: ATTACH 'test.db3' AS aux2;
! 96:
! 97: CREATE TABLE temp.t7(a, b); INSERT INTO temp.t7 VALUES(1, 2);
! 98: CREATE TABLE main.t7(a, b); INSERT INTO main.t7 VALUES(3, 4);
! 99: CREATE TABLE aux.t7(a, b); INSERT INTO aux.t7 VALUES(5, 6);
! 100: CREATE TABLE aux2.t7(a, b); INSERT INTO aux2.t7 VALUES(7, 8);
! 101:
! 102: CREATE TABLE main.t8(a, b); INSERT INTO main.t8 VALUES(1, 2);
! 103: CREATE TABLE aux.t8(a, b); INSERT INTO aux.t8 VALUES(3, 4);
! 104: CREATE TABLE aux2.t8(a, b); INSERT INTO aux2.t8 VALUES(5, 6);
! 105:
! 106: CREATE TABLE aux.t9(a, b); INSERT INTO aux.t9 VALUES(1, 2);
! 107: CREATE TABLE aux2.t9(a, b); INSERT INTO aux2.t9 VALUES(3, 4);
! 108:
! 109: CREATE TABLE aux2.t10(a, b); INSERT INTO aux2.t10 VALUES(1, 2);
! 110: } {}
! 111:
! 112:
! 113: # EVIDENCE-OF: R-09681-58560 The table-name specified as part of a
! 114: # DELETE statement within a trigger body must be unqualified.
! 115: #
! 116: # EVIDENCE-OF: R-36771-43788 In other words, the database-name. prefix
! 117: # on the table name is not allowed within triggers.
! 118: #
! 119: do_delete_tests e_delete-2.1 -error {
! 120: qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers
! 121: } {
! 122: 1 {
! 123: CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
! 124: DELETE FROM main.t2;
! 125: END;
! 126: } {}
! 127:
! 128: 2 {
! 129: CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN
! 130: DELETE FROM temp.t7 WHERE a=new.a;
! 131: END;
! 132: } {}
! 133:
! 134: 3 {
! 135: CREATE TRIGGER tr1 AFTER UPDATE ON t8 BEGIN
! 136: DELETE FROM aux2.t8 WHERE b!=a;
! 137: END;
! 138: } {}
! 139: }
! 140:
! 141: # EVIDENCE-OF: R-28818-63526 If the table to which the trigger is
! 142: # attached is not in the temp database, then DELETE statements within
! 143: # the trigger body must operate on tables within the same database as
! 144: # it.
! 145: #
! 146: # This is tested in two parts. First, check that if a table of the
! 147: # specified name does not exist, an error is raised. Secondly, test
! 148: # that if tables with the specified name exist in multiple databases,
! 149: # the local database table is used.
! 150: #
! 151: do_delete_tests e_delete-2.2.1 -error { no such table: %s } {
! 152: 1 {
! 153: CREATE TRIGGER main.tr1 AFTER INSERT ON main.t7 BEGIN
! 154: DELETE FROM t9;
! 155: END;
! 156: INSERT INTO main.t7 VALUES(1, 2);
! 157: } {main.t9}
! 158:
! 159: 2 {
! 160: CREATE TRIGGER aux.tr2 BEFORE UPDATE ON t9 BEGIN
! 161: DELETE FROM t10;
! 162: END;
! 163: UPDATE t9 SET a=1;
! 164: } {aux.t10}
! 165: }
! 166: do_execsql_test e_delete-2.2.X {
! 167: DROP TRIGGER main.tr1;
! 168: DROP TRIGGER aux.tr2;
! 169: } {}
! 170:
! 171: do_delete_tests e_delete-2.2.2 {
! 172: 1 {
! 173: CREATE TRIGGER aux.tr1 AFTER INSERT ON t8 BEGIN
! 174: DELETE FROM t9;
! 175: END;
! 176: INSERT INTO aux.t8 VALUES(1, 2);
! 177:
! 178: SELECT count(*) FROM aux.t9
! 179: UNION ALL
! 180: SELECT count(*) FROM aux2.t9;
! 181: } {0 1}
! 182:
! 183: 2 {
! 184: CREATE TRIGGER main.tr1 AFTER INSERT ON t8 BEGIN
! 185: DELETE FROM t7;
! 186: END;
! 187: INSERT INTO main.t8 VALUES(1, 2);
! 188:
! 189: SELECT count(*) FROM temp.t7
! 190: UNION ALL
! 191: SELECT count(*) FROM main.t7
! 192: UNION ALL
! 193: SELECT count(*) FROM aux.t7
! 194: UNION ALL
! 195: SELECT count(*) FROM aux2.t7;
! 196: } {1 0 1 1}
! 197: }
! 198:
! 199: # EVIDENCE-OF: R-31567-38587 If the table to which the trigger is
! 200: # attached is in the TEMP database, then the unqualified name of the
! 201: # table being deleted is resolved in the same way as it is for a
! 202: # top-level statement (by searching first the TEMP database, then the
! 203: # main database, then any other databases in the order they were
! 204: # attached).
! 205: #
! 206: do_execsql_test e_delete-2.3.0 {
! 207: DROP TRIGGER aux.tr1;
! 208: DROP TRIGGER main.tr1;
! 209: DELETE FROM main.t8 WHERE oid>1;
! 210: DELETE FROM aux.t8 WHERE oid>1;
! 211: INSERT INTO aux.t9 VALUES(1, 2);
! 212: INSERT INTO main.t7 VALUES(3, 4);
! 213: } {}
! 214: do_execsql_test e_delete-2.3.1 {
! 215: SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
! 216: SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM aux2.t7;
! 217:
! 218: SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8
! 219: UNION ALL SELECT count(*) FROM aux2.t8;
! 220:
! 221: SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM aux2.t9;
! 222:
! 223: SELECT count(*) FROM aux2.t10;
! 224: } {1 1 1 1 1 1 1 1 1 1}
! 225: do_execsql_test e_delete-2.3.2 {
! 226: CREATE TRIGGER temp.tr1 AFTER INSERT ON t7 BEGIN
! 227: DELETE FROM t7;
! 228: DELETE FROM t8;
! 229: DELETE FROM t9;
! 230: DELETE FROM t10;
! 231: END;
! 232: INSERT INTO temp.t7 VALUES('hello', 'world');
! 233: } {}
! 234: do_execsql_test e_delete-2.3.3 {
! 235: SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
! 236: SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM aux2.t7;
! 237:
! 238: SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8
! 239: UNION ALL SELECT count(*) FROM aux2.t8;
! 240:
! 241: SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM aux2.t9;
! 242:
! 243: SELECT count(*) FROM aux2.t10;
! 244: } {0 1 1 1 0 1 1 0 1 0}
! 245:
! 246: # EVIDENCE-OF: R-28691-49464 The INDEXED BY and NOT INDEXED clauses are
! 247: # not allowed on DELETE statements within triggers.
! 248: #
! 249: do_execsql_test e_delete-2.4.0 {
! 250: CREATE INDEX i8 ON t8(a, b);
! 251: } {}
! 252: do_delete_tests e_delete-2.4 -error {
! 253: the %s %s clause is not allowed on UPDATE or DELETE statements within triggers
! 254: } {
! 255: 1 {
! 256: CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
! 257: DELETE FROM t8 INDEXED BY i8 WHERE a=5;
! 258: END;
! 259: } {INDEXED BY}
! 260: 2 {
! 261: CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
! 262: DELETE FROM t8 NOT INDEXED WHERE a=5;
! 263: END;
! 264: } {NOT INDEXED}
! 265: }
! 266:
! 267: ifcapable update_delete_limit {
! 268:
! 269: # EVIDENCE-OF: R-64942-06615 The LIMIT and ORDER BY clauses (described
! 270: # below) are unsupported for DELETE statements within triggers.
! 271: #
! 272: do_delete_tests e_delete-2.5 -error { near "%s": syntax error } {
! 273: 1 {
! 274: CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
! 275: DELETE FROM t8 LIMIT 10;
! 276: END;
! 277: } {LIMIT}
! 278: 2 {
! 279: CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
! 280: DELETE FROM t8 ORDER BY a LIMIT 5;
! 281: END;
! 282: } {ORDER}
! 283: }
! 284:
! 285: # EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
! 286: # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
! 287: # of the DELETE statement is extended by the addition of optional ORDER
! 288: # BY and LIMIT clauses:
! 289: #
! 290: # EVIDENCE-OF: R-52694-53361 -- syntax diagram delete-stmt-limited
! 291: #
! 292: do_delete_tests e_delete-3.1 {
! 293: 1 "DELETE FROM t1 LIMIT 5" {}
! 294: 2 "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2" {}
! 295: 3 "DELETE FROM t1 LIMIT 2+2, 16/4" {}
! 296: 4 "DELETE FROM t1 ORDER BY x LIMIT 5" {}
! 297: 5 "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2" {}
! 298: 6 "DELETE FROM t1 ORDER BY x LIMIT 2+2, 16/4" {}
! 299: 7 "DELETE FROM t1 WHERE x>2 LIMIT 5" {}
! 300: 8 "DELETE FROM t1 WHERE x>2 LIMIT 5-1 OFFSET 2+2" {}
! 301: 9 "DELETE FROM t1 WHERE x>2 LIMIT 2+2, 16/4" {}
! 302: 10 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5" {}
! 303: 11 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5-1 OFFSET 2+2" {}
! 304: 12 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 2+2, 16/4" {}
! 305: }
! 306:
! 307: drop_all_tables
! 308: proc rebuild_t1 {} {
! 309: catchsql { DROP TABLE t1 }
! 310: execsql {
! 311: CREATE TABLE t1(a, b);
! 312: INSERT INTO t1 VALUES(1, 'one');
! 313: INSERT INTO t1 VALUES(2, 'two');
! 314: INSERT INTO t1 VALUES(3, 'three');
! 315: INSERT INTO t1 VALUES(4, 'four');
! 316: INSERT INTO t1 VALUES(5, 'five');
! 317: }
! 318: }
! 319:
! 320: # EVIDENCE-OF: R-44062-08550 If a DELETE statement has a LIMIT clause,
! 321: # the maximum number of rows that will be deleted is found by evaluating
! 322: # the accompanying expression and casting it to an integer value.
! 323: #
! 324: rebuild_t1
! 325: do_delete_tests e_delete-3.2 -repair rebuild_t1 -query {
! 326: SELECT a FROM t1
! 327: } {
! 328: 1 "DELETE FROM t1 LIMIT 3" {4 5}
! 329: 2 "DELETE FROM t1 LIMIT 1+1" {3 4 5}
! 330: 3 "DELETE FROM t1 LIMIT '4'" {5}
! 331: 4 "DELETE FROM t1 LIMIT '1.0'" {2 3 4 5}
! 332: }
! 333:
! 334: # EVIDENCE-OF: R-02661-56399 If the result of the evaluating the LIMIT
! 335: # clause cannot be losslessly converted to an integer value, it is an
! 336: # error.
! 337: #
! 338: do_delete_tests e_delete-3.3 -error { datatype mismatch } {
! 339: 1 "DELETE FROM t1 LIMIT 'abc'" {}
! 340: 2 "DELETE FROM t1 LIMIT NULL" {}
! 341: 3 "DELETE FROM t1 LIMIT X'ABCD'" {}
! 342: 4 "DELETE FROM t1 LIMIT 1.2" {}
! 343: }
! 344:
! 345: # EVIDENCE-OF: R-00598-03741 A negative LIMIT value is interpreted as
! 346: # "no limit".
! 347: #
! 348: do_delete_tests e_delete-3.4 -repair rebuild_t1 -query {
! 349: SELECT a FROM t1
! 350: } {
! 351: 1 "DELETE FROM t1 LIMIT -1" {}
! 352: 2 "DELETE FROM t1 LIMIT 2-4" {}
! 353: 3 "DELETE FROM t1 LIMIT -4.0" {}
! 354: 4 "DELETE FROM t1 LIMIT 5*-1" {}
! 355: }
! 356:
! 357: # EVIDENCE-OF: R-26377-49195 If the DELETE statement also has an OFFSET
! 358: # clause, then it is similarly evaluated and cast to an integer value.
! 359: # Again, it is an error if the value cannot be losslessly converted to
! 360: # an integer.
! 361: #
! 362: do_delete_tests e_delete-3.5 -error { datatype mismatch } {
! 363: 1 "DELETE FROM t1 LIMIT 1 OFFSET 'abc'" {}
! 364: 2 "DELETE FROM t1 LIMIT 1 OFFSET NULL" {}
! 365: 3 "DELETE FROM t1 LIMIT 1 OFFSET X'ABCD'" {}
! 366: 4 "DELETE FROM t1 LIMIT 1 OFFSET 1.2" {}
! 367: 5 "DELETE FROM t1 LIMIT 'abc', 1" {}
! 368: 6 "DELETE FROM t1 LIMIT NULL, 1" {}
! 369: 7 "DELETE FROM t1 LIMIT X'ABCD', 1" {}
! 370: 8 "DELETE FROM t1 LIMIT 1.2, 1" {}
! 371: }
! 372:
! 373:
! 374: # EVIDENCE-OF: R-64004-53814 If there is no OFFSET clause, or the
! 375: # calculated integer value is negative, the effective OFFSET value is
! 376: # zero.
! 377: #
! 378: do_delete_tests e_delete-3.6 -repair rebuild_t1 -query {
! 379: SELECT a FROM t1
! 380: } {
! 381: 1a "DELETE FROM t1 LIMIT 3 OFFSET 0" {4 5}
! 382: 1b "DELETE FROM t1 LIMIT 3" {4 5}
! 383: 1c "DELETE FROM t1 LIMIT 3 OFFSET -1" {4 5}
! 384: 2a "DELETE FROM t1 LIMIT 1+1 OFFSET 0" {3 4 5}
! 385: 2b "DELETE FROM t1 LIMIT 1+1" {3 4 5}
! 386: 2c "DELETE FROM t1 LIMIT 1+1 OFFSET 2-5" {3 4 5}
! 387: 3a "DELETE FROM t1 LIMIT '4' OFFSET 0" {5}
! 388: 3b "DELETE FROM t1 LIMIT '4'" {5}
! 389: 3c "DELETE FROM t1 LIMIT '4' OFFSET -1.0" {5}
! 390: 4a "DELETE FROM t1 LIMIT '1.0' OFFSET 0" {2 3 4 5}
! 391: 4b "DELETE FROM t1 LIMIT '1.0'" {2 3 4 5}
! 392: 4c "DELETE FROM t1 LIMIT '1.0' OFFSET -11" {2 3 4 5}
! 393: }
! 394:
! 395: # EVIDENCE-OF: R-48141-52334 If the DELETE statement has an ORDER BY
! 396: # clause, then all rows that would be deleted in the absence of the
! 397: # LIMIT clause are sorted according to the ORDER BY. The first M rows,
! 398: # where M is the value found by evaluating the OFFSET clause expression,
! 399: # are skipped, and the following N, where N is the value of the LIMIT
! 400: # expression, are deleted.
! 401: #
! 402: do_delete_tests e_delete-3.7 -repair rebuild_t1 -query {
! 403: SELECT a FROM t1
! 404: } {
! 405: 1 "DELETE FROM t1 ORDER BY b LIMIT 2" {1 2 3}
! 406: 2 "DELETE FROM t1 ORDER BY length(b), a LIMIT 3" {3 5}
! 407: 3 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 0" {1 2 3 4}
! 408: 4 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 1" {1 2 3 5}
! 409: 5 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 2" {1 2 4 5}
! 410: }
! 411:
! 412: # EVIDENCE-OF: R-64535-08414 If there are less than N rows remaining
! 413: # after taking the OFFSET clause into account, or if the LIMIT clause
! 414: # evaluated to a negative value, then all remaining rows are deleted.
! 415: #
! 416: do_delete_tests e_delete-3.8 -repair rebuild_t1 -query {
! 417: SELECT a FROM t1
! 418: } {
! 419: 1 "DELETE FROM t1 ORDER BY a ASC LIMIT 10" {}
! 420: 2 "DELETE FROM t1 ORDER BY a ASC LIMIT -1" {}
! 421: 3 "DELETE FROM t1 ORDER BY a ASC LIMIT 4 OFFSET 2" {1 2}
! 422: }
! 423:
! 424: # EVIDENCE-OF: R-37284-06965 If the DELETE statement has no ORDER BY
! 425: # clause, then all rows that would be deleted in the absence of the
! 426: # LIMIT clause are assembled in an arbitrary order before applying the
! 427: # LIMIT and OFFSET clauses to determine the subset that are actually
! 428: # deleted.
! 429: #
! 430: # In practice, the "arbitrary order" is rowid order.
! 431: #
! 432: do_delete_tests e_delete-3.9 -repair rebuild_t1 -query {
! 433: SELECT a FROM t1
! 434: } {
! 435: 1 "DELETE FROM t1 LIMIT 2" {3 4 5}
! 436: 2 "DELETE FROM t1 LIMIT 3" {4 5}
! 437: 3 "DELETE FROM t1 LIMIT 1 OFFSET 0" {2 3 4 5}
! 438: 4 "DELETE FROM t1 LIMIT 1 OFFSET 1" {1 3 4 5}
! 439: 5 "DELETE FROM t1 LIMIT 1 OFFSET 2" {1 2 4 5}
! 440: }
! 441:
! 442:
! 443: # EVIDENCE-OF: R-07548-13422 The ORDER BY clause on a DELETE statement
! 444: # is used only to determine which rows fall within the LIMIT. The order
! 445: # in which rows are deleted is arbitrary and is not influenced by the
! 446: # ORDER BY clause.
! 447: #
! 448: # In practice, rows are always deleted in rowid order.
! 449: #
! 450: do_delete_tests e_delete-3.10 -repair {
! 451: rebuild_t1
! 452: catchsql { DROP TABLE t1log }
! 453: execsql {
! 454: CREATE TABLE t1log(x);
! 455: CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
! 456: INSERT INTO t1log VALUES(old.a);
! 457: END;
! 458: }
! 459: } -query {
! 460: SELECT x FROM t1log
! 461: } {
! 462: 1 "DELETE FROM t1 ORDER BY a DESC LIMIT 2" {4 5}
! 463: 2 "DELETE FROM t1 ORDER BY a DESC LIMIT -1" {1 2 3 4 5}
! 464: 3 "DELETE FROM t1 ORDER BY a ASC LIMIT 2" {1 2}
! 465: 4 "DELETE FROM t1 ORDER BY a ASC LIMIT -1" {1 2 3 4 5}
! 466: }
! 467:
! 468: }
! 469:
! 470: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_delete.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