File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / trigger1.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    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>