Annotation of embedaddon/sqlite3/test/trigger1.test, revision 1.1
1.1 ! misho 1: # The author disclaims copyright to this source code. In place of
! 2: # a legal notice, here is a blessing:
! 3: #
! 4: # May you do good and not evil.
! 5: # May you find forgiveness for yourself and forgive others.
! 6: # May you share freely, never taking more than you give.
! 7: #
! 8: #***********************************************************************
! 9: #
! 10: # This file tests creating and dropping triggers, and interaction thereof
! 11: # with the database COMMIT/ROLLBACK logic.
! 12: #
! 13: # 1. CREATE and DROP TRIGGER tests
! 14: # trig-1.1: Error if table does not exist
! 15: # trig-1.2: Error if trigger already exists
! 16: # trig-1.3: Created triggers are deleted if the transaction is rolled back
! 17: # trig-1.4: DROP TRIGGER removes trigger
! 18: # trig-1.5: Dropped triggers are restored if the transaction is rolled back
! 19: # trig-1.6: Error if dropped trigger doesn't exist
! 20: # trig-1.7: Dropping the table automatically drops all triggers
! 21: # trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master
! 22: # trig-1.9: Ensure that we cannot create a trigger on sqlite_master
! 23: # trig-1.10:
! 24: # trig-1.11:
! 25: # trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
! 26: # trig-1.13: Ensure that AFTER triggers cannot be created on views
! 27: # trig-1.14: Ensure that BEFORE triggers cannot be created on views
! 28: #
! 29:
! 30: set testdir [file dirname $argv0]
! 31: source $testdir/tester.tcl
! 32: ifcapable {!trigger} {
! 33: finish_test
! 34: return
! 35: }
! 36:
! 37: do_test trigger1-1.1.1 {
! 38: catchsql {
! 39: CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
! 40: SELECT * from sqlite_master;
! 41: END;
! 42: }
! 43: } {1 {no such table: main.no_such_table}}
! 44:
! 45: ifcapable tempdb {
! 46: do_test trigger1-1.1.2 {
! 47: catchsql {
! 48: CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN
! 49: SELECT * from sqlite_master;
! 50: END;
! 51: }
! 52: } {1 {no such table: no_such_table}}
! 53: }
! 54:
! 55: execsql {
! 56: CREATE TABLE t1(a);
! 57: }
! 58: do_test trigger1-1.1.3 {
! 59: catchsql {
! 60: CREATE TRIGGER trig UPDATE ON t1 FOR EACH STATEMENT BEGIN
! 61: SELECT * FROM sqlite_master;
! 62: END;
! 63: }
! 64: } {1 {near "STATEMENT": syntax error}}
! 65: execsql {
! 66: CREATE TRIGGER tr1 INSERT ON t1 BEGIN
! 67: INSERT INTO t1 values(1);
! 68: END;
! 69: }
! 70: do_test trigger1-1.2.0 {
! 71: catchsql {
! 72: CREATE TRIGGER IF NOT EXISTS tr1 DELETE ON t1 BEGIN
! 73: SELECT * FROM sqlite_master;
! 74: END
! 75: }
! 76: } {0 {}}
! 77: do_test trigger1-1.2.1 {
! 78: catchsql {
! 79: CREATE TRIGGER tr1 DELETE ON t1 BEGIN
! 80: SELECT * FROM sqlite_master;
! 81: END
! 82: }
! 83: } {1 {trigger tr1 already exists}}
! 84: do_test trigger1-1.2.2 {
! 85: catchsql {
! 86: CREATE TRIGGER "tr1" DELETE ON t1 BEGIN
! 87: SELECT * FROM sqlite_master;
! 88: END
! 89: }
! 90: } {1 {trigger "tr1" already exists}}
! 91: do_test trigger1-1.2.3 {
! 92: catchsql {
! 93: CREATE TRIGGER [tr1] DELETE ON t1 BEGIN
! 94: SELECT * FROM sqlite_master;
! 95: END
! 96: }
! 97: } {1 {trigger [tr1] already exists}}
! 98:
! 99: do_test trigger1-1.3 {
! 100: catchsql {
! 101: BEGIN;
! 102: CREATE TRIGGER tr2 INSERT ON t1 BEGIN
! 103: SELECT * from sqlite_master; END;
! 104: ROLLBACK;
! 105: CREATE TRIGGER tr2 INSERT ON t1 BEGIN
! 106: SELECT * from sqlite_master; END;
! 107: }
! 108: } {0 {}}
! 109:
! 110: do_test trigger1-1.4 {
! 111: catchsql {
! 112: DROP TRIGGER IF EXISTS tr1;
! 113: CREATE TRIGGER tr1 DELETE ON t1 BEGIN
! 114: SELECT * FROM sqlite_master;
! 115: END
! 116: }
! 117: } {0 {}}
! 118:
! 119: do_test trigger1-1.5 {
! 120: execsql {
! 121: BEGIN;
! 122: DROP TRIGGER tr2;
! 123: ROLLBACK;
! 124: DROP TRIGGER tr2;
! 125: }
! 126: } {}
! 127:
! 128: do_test trigger1-1.6.1 {
! 129: catchsql {
! 130: DROP TRIGGER IF EXISTS biggles;
! 131: }
! 132: } {0 {}}
! 133:
! 134: do_test trigger1-1.6.2 {
! 135: catchsql {
! 136: DROP TRIGGER biggles;
! 137: }
! 138: } {1 {no such trigger: biggles}}
! 139:
! 140: do_test trigger1-1.7 {
! 141: catchsql {
! 142: DROP TABLE t1;
! 143: DROP TRIGGER tr1;
! 144: }
! 145: } {1 {no such trigger: tr1}}
! 146:
! 147: ifcapable tempdb {
! 148: execsql {
! 149: CREATE TEMP TABLE temp_table(a);
! 150: }
! 151: do_test trigger1-1.8 {
! 152: execsql {
! 153: CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
! 154: SELECT * from sqlite_master;
! 155: END;
! 156: SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
! 157: }
! 158: } {0}
! 159: }
! 160:
! 161: do_test trigger1-1.9 {
! 162: catchsql {
! 163: CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
! 164: SELECT * FROM sqlite_master;
! 165: END;
! 166: }
! 167: } {1 {cannot create trigger on system table}}
! 168:
! 169: # Check to make sure that a DELETE statement within the body of
! 170: # a trigger does not mess up the DELETE that caused the trigger to
! 171: # run in the first place.
! 172: #
! 173: do_test trigger1-1.10 {
! 174: execsql {
! 175: create table t1(a,b);
! 176: insert into t1 values(1,'a');
! 177: insert into t1 values(2,'b');
! 178: insert into t1 values(3,'c');
! 179: insert into t1 values(4,'d');
! 180: create trigger r1 after delete on t1 for each row begin
! 181: delete from t1 WHERE a=old.a+2;
! 182: end;
! 183: delete from t1 where a=1 OR a=3;
! 184: select * from t1;
! 185: drop table t1;
! 186: }
! 187: } {2 b 4 d}
! 188:
! 189: do_test trigger1-1.11 {
! 190: execsql {
! 191: create table t1(a,b);
! 192: insert into t1 values(1,'a');
! 193: insert into t1 values(2,'b');
! 194: insert into t1 values(3,'c');
! 195: insert into t1 values(4,'d');
! 196: create trigger r1 after update on t1 for each row begin
! 197: delete from t1 WHERE a=old.a+2;
! 198: end;
! 199: update t1 set b='x-' || b where a=1 OR a=3;
! 200: select * from t1;
! 201: drop table t1;
! 202: }
! 203: } {1 x-a 2 b 4 d}
! 204:
! 205: # Ensure that we cannot create INSTEAD OF triggers on tables
! 206: do_test trigger1-1.12 {
! 207: catchsql {
! 208: create table t1(a,b);
! 209: create trigger t1t instead of update on t1 for each row begin
! 210: delete from t1 WHERE a=old.a+2;
! 211: end;
! 212: }
! 213: } {1 {cannot create INSTEAD OF trigger on table: main.t1}}
! 214:
! 215: ifcapable view {
! 216: # Ensure that we cannot create BEFORE triggers on views
! 217: do_test trigger1-1.13 {
! 218: catchsql {
! 219: create view v1 as select * from t1;
! 220: create trigger v1t before update on v1 for each row begin
! 221: delete from t1 WHERE a=old.a+2;
! 222: end;
! 223: }
! 224: } {1 {cannot create BEFORE trigger on view: main.v1}}
! 225: # Ensure that we cannot create AFTER triggers on views
! 226: do_test trigger1-1.14 {
! 227: catchsql {
! 228: drop view v1;
! 229: create view v1 as select * from t1;
! 230: create trigger v1t AFTER update on v1 for each row begin
! 231: delete from t1 WHERE a=old.a+2;
! 232: end;
! 233: }
! 234: } {1 {cannot create AFTER trigger on view: main.v1}}
! 235: } ;# ifcapable view
! 236:
! 237: # Check for memory leaks in the trigger parser
! 238: #
! 239: do_test trigger1-2.1 {
! 240: catchsql {
! 241: CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
! 242: SELECT * FROM; -- Syntax error
! 243: END;
! 244: }
! 245: } {1 {near ";": syntax error}}
! 246: do_test trigger1-2.2 {
! 247: catchsql {
! 248: CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
! 249: SELECT * FROM t1;
! 250: SELECT * FROM; -- Syntax error
! 251: END;
! 252: }
! 253: } {1 {near ";": syntax error}}
! 254:
! 255: # Create a trigger that refers to a table that might not exist.
! 256: #
! 257: ifcapable tempdb {
! 258: do_test trigger1-3.1 {
! 259: execsql {
! 260: CREATE TEMP TABLE t2(x,y);
! 261: }
! 262: catchsql {
! 263: CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
! 264: INSERT INTO t2 VALUES(NEW.a,NEW.b);
! 265: END;
! 266: }
! 267: } {0 {}}
! 268: do_test trigger-3.2 {
! 269: catchsql {
! 270: INSERT INTO t1 VALUES(1,2);
! 271: SELECT * FROM t2;
! 272: }
! 273: } {1 {no such table: main.t2}}
! 274: do_test trigger-3.3 {
! 275: db close
! 276: set rc [catch {sqlite3 db test.db} err]
! 277: if {$rc} {lappend rc $err}
! 278: set rc
! 279: } {0}
! 280: do_test trigger-3.4 {
! 281: catchsql {
! 282: INSERT INTO t1 VALUES(1,2);
! 283: SELECT * FROM t2;
! 284: }
! 285: } {1 {no such table: main.t2}}
! 286: do_test trigger-3.5 {
! 287: catchsql {
! 288: CREATE TEMP TABLE t2(x,y);
! 289: INSERT INTO t1 VALUES(1,2);
! 290: SELECT * FROM t2;
! 291: }
! 292: } {1 {no such table: main.t2}}
! 293: do_test trigger-3.6 {
! 294: catchsql {
! 295: DROP TRIGGER r1;
! 296: CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
! 297: INSERT INTO t2 VALUES(NEW.a,NEW.b);
! 298: END;
! 299: INSERT INTO t1 VALUES(1,2);
! 300: SELECT * FROM t2;
! 301: }
! 302: } {0 {1 2}}
! 303: do_test trigger-3.7 {
! 304: execsql {
! 305: DROP TABLE t2;
! 306: CREATE TABLE t2(x,y);
! 307: SELECT * FROM t2;
! 308: }
! 309: } {}
! 310:
! 311: # There are two versions of trigger-3.8 and trigger-3.9. One that uses
! 312: # compound SELECT statements, and another that does not.
! 313: ifcapable compound {
! 314: do_test trigger1-3.8 {
! 315: execsql {
! 316: INSERT INTO t1 VALUES(3,4);
! 317: SELECT * FROM t1 UNION ALL SELECT * FROM t2;
! 318: }
! 319: } {1 2 3 4 3 4}
! 320: do_test trigger1-3.9 {
! 321: db close
! 322: sqlite3 db test.db
! 323: execsql {
! 324: INSERT INTO t1 VALUES(5,6);
! 325: SELECT * FROM t1 UNION ALL SELECT * FROM t2;
! 326: }
! 327: } {1 2 3 4 5 6 3 4}
! 328: } ;# ifcapable compound
! 329: ifcapable !compound {
! 330: do_test trigger1-3.8 {
! 331: execsql {
! 332: INSERT INTO t1 VALUES(3,4);
! 333: SELECT * FROM t1;
! 334: SELECT * FROM t2;
! 335: }
! 336: } {1 2 3 4 3 4}
! 337: do_test trigger1-3.9 {
! 338: db close
! 339: sqlite3 db test.db
! 340: execsql {
! 341: INSERT INTO t1 VALUES(5,6);
! 342: SELECT * FROM t1;
! 343: SELECT * FROM t2;
! 344: }
! 345: } {1 2 3 4 5 6 3 4}
! 346: } ;# ifcapable !compound
! 347:
! 348: do_test trigger1-4.1 {
! 349: execsql {
! 350: CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
! 351: INSERT INTO t2 VALUES(NEW.a,NEW.b);
! 352: END;
! 353: INSERT INTO t1 VALUES(7,8);
! 354: SELECT * FROM t2;
! 355: }
! 356: } {3 4 7 8}
! 357: do_test trigger1-4.2 {
! 358: sqlite3 db2 test.db
! 359: execsql {
! 360: INSERT INTO t1 VALUES(9,10);
! 361: } db2;
! 362: db2 close
! 363: execsql {
! 364: SELECT * FROM t2;
! 365: }
! 366: } {3 4 7 8}
! 367: do_test trigger1-4.3 {
! 368: execsql {
! 369: DROP TABLE t1;
! 370: SELECT * FROM t2;
! 371: };
! 372: } {3 4 7 8}
! 373: do_test trigger1-4.4 {
! 374: db close
! 375: sqlite3 db test.db
! 376: execsql {
! 377: SELECT * FROM t2;
! 378: };
! 379: } {3 4 7 8}
! 380: } else {
! 381: execsql {
! 382: CREATE TABLE t2(x,y);
! 383: DROP TABLE t1;
! 384: INSERT INTO t2 VALUES(3, 4);
! 385: INSERT INTO t2 VALUES(7, 8);
! 386: }
! 387: }
! 388:
! 389:
! 390: integrity_check trigger1-5.1
! 391:
! 392: # Create a trigger with the same name as a table. Make sure the
! 393: # trigger works. Then drop the trigger. Make sure the table is
! 394: # still there.
! 395: #
! 396: set view_v1 {}
! 397: ifcapable view {
! 398: set view_v1 {view v1}
! 399: }
! 400: do_test trigger1-6.1 {
! 401: execsql {SELECT type, name FROM sqlite_master}
! 402: } [concat $view_v1 {table t2}]
! 403: do_test trigger1-6.2 {
! 404: execsql {
! 405: CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN
! 406: SELECT RAISE(ABORT,'deletes are not permitted');
! 407: END;
! 408: SELECT type, name FROM sqlite_master;
! 409: }
! 410: } [concat $view_v1 {table t2 trigger t2}]
! 411: do_test trigger1-6.3 {
! 412: catchsql {DELETE FROM t2}
! 413: } {1 {deletes are not permitted}}
! 414: do_test trigger1-6.4 {
! 415: execsql {SELECT * FROM t2}
! 416: } {3 4 7 8}
! 417: do_test trigger1-6.5 {
! 418: db close
! 419: sqlite3 db test.db
! 420: execsql {SELECT type, name FROM sqlite_master}
! 421: } [concat $view_v1 {table t2 trigger t2}]
! 422: do_test trigger1-6.6 {
! 423: execsql {
! 424: DROP TRIGGER t2;
! 425: SELECT type, name FROM sqlite_master;
! 426: }
! 427: } [concat $view_v1 {table t2}]
! 428: do_test trigger1-6.7 {
! 429: execsql {SELECT * FROM t2}
! 430: } {3 4 7 8}
! 431: do_test trigger1-6.8 {
! 432: db close
! 433: sqlite3 db test.db
! 434: execsql {SELECT * FROM t2}
! 435: } {3 4 7 8}
! 436:
! 437: integrity_check trigger-7.1
! 438:
! 439: # Check to make sure the name of a trigger can be quoted so that keywords
! 440: # can be used as trigger names. Ticket #468
! 441: #
! 442: do_test trigger1-8.1 {
! 443: execsql {
! 444: CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;
! 445: SELECT name FROM sqlite_master WHERE type='trigger';
! 446: }
! 447: } {trigger}
! 448: do_test trigger1-8.2 {
! 449: execsql {
! 450: DROP TRIGGER 'trigger';
! 451: SELECT name FROM sqlite_master WHERE type='trigger';
! 452: }
! 453: } {}
! 454: do_test trigger1-8.3 {
! 455: execsql {
! 456: CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;
! 457: SELECT name FROM sqlite_master WHERE type='trigger';
! 458: }
! 459: } {trigger}
! 460: do_test trigger1-8.4 {
! 461: execsql {
! 462: DROP TRIGGER "trigger";
! 463: SELECT name FROM sqlite_master WHERE type='trigger';
! 464: }
! 465: } {}
! 466: do_test trigger1-8.5 {
! 467: execsql {
! 468: CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;
! 469: SELECT name FROM sqlite_master WHERE type='trigger';
! 470: }
! 471: } {trigger}
! 472: do_test trigger1-8.6 {
! 473: execsql {
! 474: DROP TRIGGER [trigger];
! 475: SELECT name FROM sqlite_master WHERE type='trigger';
! 476: }
! 477: } {}
! 478:
! 479: ifcapable conflict {
! 480: # Make sure REPLACE works inside of triggers.
! 481: #
! 482: # There are two versions of trigger-9.1 and trigger-9.2. One that uses
! 483: # compound SELECT statements, and another that does not.
! 484: ifcapable compound {
! 485: do_test trigger1-9.1 {
! 486: execsql {
! 487: CREATE TABLE t3(a,b);
! 488: CREATE TABLE t4(x UNIQUE, b);
! 489: CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
! 490: REPLACE INTO t4 VALUES(new.a,new.b);
! 491: END;
! 492: INSERT INTO t3 VALUES(1,2);
! 493: SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
! 494: }
! 495: } {1 2 99 99 1 2}
! 496: do_test trigger1-9.2 {
! 497: execsql {
! 498: INSERT INTO t3 VALUES(1,3);
! 499: SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
! 500: }
! 501: } {1 2 1 3 99 99 1 3}
! 502: } else {
! 503: do_test trigger1-9.1 {
! 504: execsql {
! 505: CREATE TABLE t3(a,b);
! 506: CREATE TABLE t4(x UNIQUE, b);
! 507: CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
! 508: REPLACE INTO t4 VALUES(new.a,new.b);
! 509: END;
! 510: INSERT INTO t3 VALUES(1,2);
! 511: SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
! 512: }
! 513: } {1 2 99 99 1 2}
! 514: do_test trigger1-9.2 {
! 515: execsql {
! 516: INSERT INTO t3 VALUES(1,3);
! 517: SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
! 518: }
! 519: } {1 2 1 3 99 99 1 3}
! 520: }
! 521: execsql {
! 522: DROP TABLE t3;
! 523: DROP TABLE t4;
! 524: }
! 525: }
! 526:
! 527:
! 528: # Ticket #764. At one stage TEMP triggers would fail to re-install when the
! 529: # schema was reloaded. The following tests ensure that TEMP triggers are
! 530: # correctly re-installed.
! 531: #
! 532: # Also verify that references within trigger programs are resolved at
! 533: # statement compile time, not trigger installation time. This means, for
! 534: # example, that you can drop and re-create tables referenced by triggers.
! 535: ifcapable tempdb&&attach {
! 536: do_test trigger1-10.0 {
! 537: forcedelete test2.db
! 538: forcedelete test2.db-journal
! 539: execsql {
! 540: ATTACH 'test2.db' AS aux;
! 541: }
! 542: } {}
! 543: do_test trigger1-10.1 {
! 544: execsql {
! 545: CREATE TABLE main.t4(a, b, c);
! 546: CREATE TABLE temp.t4(a, b, c);
! 547: CREATE TABLE aux.t4(a, b, c);
! 548: CREATE TABLE insert_log(db, a, b, c);
! 549: }
! 550: } {}
! 551: do_test trigger1-10.2 {
! 552: execsql {
! 553: CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN
! 554: INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
! 555: END;
! 556: CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN
! 557: INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
! 558: END;
! 559: CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN
! 560: INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
! 561: END;
! 562: }
! 563: } {}
! 564: do_test trigger1-10.3 {
! 565: execsql {
! 566: INSERT INTO main.t4 VALUES(1, 2, 3);
! 567: INSERT INTO temp.t4 VALUES(4, 5, 6);
! 568: INSERT INTO aux.t4 VALUES(7, 8, 9);
! 569: }
! 570: } {}
! 571: do_test trigger1-10.4 {
! 572: execsql {
! 573: SELECT * FROM insert_log;
! 574: }
! 575: } {main 1 2 3 temp 4 5 6 aux 7 8 9}
! 576: do_test trigger1-10.5 {
! 577: execsql {
! 578: BEGIN;
! 579: INSERT INTO main.t4 VALUES(1, 2, 3);
! 580: INSERT INTO temp.t4 VALUES(4, 5, 6);
! 581: INSERT INTO aux.t4 VALUES(7, 8, 9);
! 582: ROLLBACK;
! 583: }
! 584: } {}
! 585: do_test trigger1-10.6 {
! 586: execsql {
! 587: SELECT * FROM insert_log;
! 588: }
! 589: } {main 1 2 3 temp 4 5 6 aux 7 8 9}
! 590: do_test trigger1-10.7 {
! 591: execsql {
! 592: DELETE FROM insert_log;
! 593: INSERT INTO main.t4 VALUES(11, 12, 13);
! 594: INSERT INTO temp.t4 VALUES(14, 15, 16);
! 595: INSERT INTO aux.t4 VALUES(17, 18, 19);
! 596: }
! 597: } {}
! 598: do_test trigger1-10.8 {
! 599: execsql {
! 600: SELECT * FROM insert_log;
! 601: }
! 602: } {main 11 12 13 temp 14 15 16 aux 17 18 19}
! 603: do_test trigger1-10.8 {
! 604: # Drop and re-create the insert_log table in a different database. Note
! 605: # that we can change the column names because the trigger programs don't
! 606: # use them explicitly.
! 607: execsql {
! 608: DROP TABLE insert_log;
! 609: CREATE TABLE aux.insert_log(db, d, e, f);
! 610: }
! 611: } {}
! 612: do_test trigger1-10.10 {
! 613: execsql {
! 614: INSERT INTO main.t4 VALUES(21, 22, 23);
! 615: INSERT INTO temp.t4 VALUES(24, 25, 26);
! 616: INSERT INTO aux.t4 VALUES(27, 28, 29);
! 617: }
! 618: } {}
! 619: do_test trigger1-10.11 {
! 620: execsql {
! 621: SELECT * FROM insert_log;
! 622: }
! 623: } {main 21 22 23 temp 24 25 26 aux 27 28 29}
! 624: }
! 625:
! 626: do_test trigger1-11.1 {
! 627: catchsql {SELECT raise(abort,'message');}
! 628: } {1 {RAISE() may only be used within a trigger-program}}
! 629:
! 630: do_test trigger1-15.1 {
! 631: execsql {
! 632: CREATE TABLE tA(a INTEGER PRIMARY KEY, b, c);
! 633: CREATE TRIGGER tA_trigger BEFORE UPDATE ON "tA" BEGIN SELECT 1; END;
! 634: INSERT INTO tA VALUES(1, 2, 3);
! 635: }
! 636: catchsql { UPDATE tA SET a = 'abc' }
! 637: } {1 {datatype mismatch}}
! 638: do_test trigger1-15.2 {
! 639: catchsql { INSERT INTO tA VALUES('abc', 2, 3) }
! 640: } {1 {datatype mismatch}}
! 641:
! 642: # Ticket #3947: Do not allow qualified table names on INSERT, UPDATE, and
! 643: # DELETE statements within triggers. Actually, this has never been allowed
! 644: # by the grammar. But the error message is confusing: one simply gets a
! 645: # "syntax error". That has now been changed to give a full error message.
! 646: #
! 647: do_test trigger1-16.1 {
! 648: db eval {
! 649: CREATE TABLE t16(a,b,c);
! 650: CREATE INDEX t16a ON t16(a);
! 651: CREATE INDEX t16b ON t16(b);
! 652: }
! 653: catchsql {
! 654: CREATE TRIGGER main.t16err1 AFTER INSERT ON tA BEGIN
! 655: INSERT INTO main.t16 VALUES(1,2,3);
! 656: END;
! 657: }
! 658: } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
! 659: do_test trigger1-16.2 {
! 660: catchsql {
! 661: CREATE TRIGGER main.t16err2 AFTER INSERT ON tA BEGIN
! 662: UPDATE main.t16 SET rowid=rowid+1;
! 663: END;
! 664: }
! 665: } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
! 666: do_test trigger1-16.3 {
! 667: catchsql {
! 668: CREATE TRIGGER main.t16err3 AFTER INSERT ON tA BEGIN
! 669: DELETE FROM main.t16;
! 670: END;
! 671: }
! 672: } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
! 673: do_test trigger1-16.4 {
! 674: catchsql {
! 675: CREATE TRIGGER main.t16err4 AFTER INSERT ON tA BEGIN
! 676: UPDATE t16 NOT INDEXED SET rowid=rowid+1;
! 677: END;
! 678: }
! 679: } {1 {the NOT INDEXED clause is not allowed on UPDATE or DELETE statements within triggers}}
! 680: do_test trigger1-16.5 {
! 681: catchsql {
! 682: CREATE TRIGGER main.t16err5 AFTER INSERT ON tA BEGIN
! 683: UPDATE t16 INDEXED BY t16a SET rowid=rowid+1 WHERE a=1;
! 684: END;
! 685: }
! 686: } {1 {the INDEXED BY clause is not allowed on UPDATE or DELETE statements within triggers}}
! 687: do_test trigger1-16.6 {
! 688: catchsql {
! 689: CREATE TRIGGER main.t16err6 AFTER INSERT ON tA BEGIN
! 690: DELETE FROM t16 NOT INDEXED WHERE a=123;
! 691: END;
! 692: }
! 693: } {1 {the NOT INDEXED clause is not allowed on UPDATE or DELETE statements within triggers}}
! 694: do_test trigger1-16.7 {
! 695: catchsql {
! 696: CREATE TRIGGER main.t16err7 AFTER INSERT ON tA BEGIN
! 697: DELETE FROM t16 INDEXED BY t16a WHERE a=123;
! 698: END;
! 699: }
! 700: } {1 {the INDEXED BY clause is not allowed on UPDATE or DELETE statements within triggers}}
! 701:
! 702: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trigger1.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