SQLite

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.154 2003/05/17 19:04:04 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs

    Index *pIdx;
    Trigger *pTrigger;
    sqliteBeginWriteOperation(pParse, 0, pTable->iDb);

    /* Drop all triggers associated with the table being dropped */
    pTrigger = pTable->pTrigger;
    while( pTrigger ){

      assert( pTrigger->iDb==pTable->iDb || pTrigger->iDb==1 );



      sqliteDropTriggerPtr(pParse, pTrigger, 1);
      if( pParse->explain ){
        pTrigger = pTrigger->pNext;
      }else{
        pTrigger = pTable->pTrigger;
      }
    }

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.98 2003/05/17 19:04:04 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){

  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
  A->iColumn = OE_Fail;
  sqliteExprSpan(A, &X, &Y);
}

////////////////////////  DROP TRIGGER statement //////////////////////////////
cmd ::= DROP TRIGGER nm(X) dbnm(D). {
  sqliteDropTrigger(pParse,sqliteSrcListAppend(0,&X,&D));
}

//////////////////////// ATTACH DATABASE file AS name /////////////////////////
cmd ::= ATTACH database_kw_opt ids(F) AS nm(D). {
  sqliteAttach(pParse, &F, &D);
}

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.186 2003/05/17 19:04:04 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"

 * The "step_list" member points to the first element of a linked list
 * containing the SQL statements specified as the trigger program.
 */
struct Trigger {
  char *name;             /* The name of the trigger                        */
  char *table;            /* The table or view to which the trigger applies */
  u8 iDb;                 /* Database containing this trigger               */
  u8 iTabDb;              /* Database containing Trigger.table              */
  u8 op;                  /* One of TK_DELETE, TK_UPDATE, TK_INSERT         */
  u8 tr_tm;               /* One of TK_BEFORE, TK_AFTER */
  Expr *pWhen;            /* The WHEN clause of the expresion (may be NULL) */
  IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
                             the <column-list> is stored here */
  int foreach;            /* One of TK_ROW or TK_STATEMENT */

void sqliteRegisterBuiltinFunctions(sqlite*);
int sqliteSafetyOn(sqlite*);
int sqliteSafetyOff(sqlite*);
int sqliteSafetyCheck(sqlite*);
void sqliteChangeCookie(sqlite*, Vdbe*);
void sqliteBeginTrigger(Parse*, Token*,int,int,IdList*,SrcList*,int,Expr*,int);
void sqliteFinishTrigger(Parse*, TriggerStep*, Token*);
void sqliteDropTrigger(Parse*, SrcList*);
void sqliteDropTriggerPtr(Parse*, Trigger*, int);
int sqliteTriggersExist(Parse* , Trigger* , int , int , int, ExprList*);
int sqliteCodeRowTrigger(Parse*, int, ExprList*, int, Table *, int, int, 
                         int, int);
void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
void sqliteDeleteTriggerStep(TriggerStep*);
TriggerStep *sqliteTriggerSelectStep(Select*);
TriggerStep *sqliteTriggerInsertStep(Token*, IdList*, ExprList*, Select*, int);

  nt = (Trigger*)sqliteMalloc(sizeof(Trigger));
  if( nt==0 ) goto trigger_cleanup;
  nt->name = zName;
  zName = 0;
  nt->table = sqliteStrDup(pTableName->a[0].zName);
  if( sqlite_malloc_failed ) goto trigger_cleanup;
  nt->iDb = iDb;
  nt->iTabDb = tab->iDb;
  nt->op = op;
  nt->tr_tm = tr_tm;
  nt->pWhen = sqliteExprDup(pWhen);
  nt->pColumns = sqliteIdListDup(pColumns);
  nt->foreach = foreach;
  assert( pParse->pNewTrigger==0 );
  pParse->pNewTrigger = nt;

    int addr;
    Vdbe *v;

    /* Make an entry in the sqlite_master table */
    v = sqliteGetVdbe(pParse);
    if( v==0 ) goto triggerfinish_cleanup;
    sqliteBeginWriteOperation(pParse, 0, 0);
    sqliteOpenMasterTable(v, nt->iDb);
    addr = sqliteVdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
    sqliteVdbeChangeP3(v, addr+2, nt->name, 0); 
    sqliteVdbeChangeP3(v, addr+3, nt->table, 0); 
    sqliteVdbeChangeP3(v, addr+5, pAll->z, pAll->n);
    if( nt->iDb==0 ){
      sqliteChangeCookie(db, v);
    }

  sqliteIdListDelete(pTrigger->pColumns);
  sqliteFree(pTrigger);
}

/*
 * This function is called to drop a trigger from the database schema. 
 *
 * This may be called directly from the parser and therefore identifies
 * the trigger by name.  The sqliteDropTriggerPtr() routine does the
 * same job as this routine except it take a spointer to the trigger
 * instead of the trigger name.
 *
 * Note that this function does not delete the trigger entirely. Instead it
 * removes it from the internal schema and places it in the trigDrop hash 
 * table. This is so that the trigger can be restored into the database schema
 * if the transaction is rolled back.
 */
void sqliteDropTrigger(Parse *pParse, SrcList *pName){
  Trigger *pTrigger;


  int i;
  const char *zDb;
  const char *zName;
  int nName;
  sqlite *db = pParse->db;

  if( sqlite_malloc_failed ) goto drop_trigger_cleanup;

    pTrigger = sqliteHashFind(&(db->aDb[j].trigHash), zName, nName+1);
    if( pTrigger ) break;
  }
  if( !pTrigger ){
    sqliteErrorMsg(pParse, "no such trigger: %S", pName, 0);
    goto drop_trigger_cleanup;
  }
  sqliteDropTriggerPtr(pParse, pTrigger, 0);

drop_trigger_cleanup:
  sqliteSrcListDelete(pName);
}

/*
** Drop a trigger given a pointer to that trigger.  If nested is false,
** then also generate code to remove the trigger from the SQLITE_MASTER
** table.
*/
void sqliteDropTriggerPtr(Parse *pParse, Trigger *pTrigger, int nested){
  Table   *pTable;
  Vdbe *v;
  sqlite *db = pParse->db;

  assert( pTrigger->iDb<db->nDb );
  if( pTrigger->iDb>=2 ){
    sqliteErrorMsg(pParse, "triggers may not be removed from "
       "auxiliary database %s", db->aDb[pTrigger->iDb].zName);
    return;
  }
  pTable = sqliteFindTable(db, pTrigger->table,db->aDb[pTrigger->iTabDb].zName);
  assert(pTable);
  assert( pTable->iDb==pTrigger->iDb || pTrigger->iDb==1 );
#ifndef SQLITE_OMIT_AUTHORIZATION
  {
    int code = SQLITE_DROP_TRIGGER;
    const char *zDb = db->aDb[pTrigger->iDb].zName;
    const char *zTab = SCHEMA_TABLE(pTrigger->iDb);
    if( pTrigger->iDb ) code = SQLITE_DROP_TEMP_TRIGGER;
    if( sqliteAuthCheck(pParse, code, pTrigger->name, pTable->zName, zDb) ||
      sqliteAuthCheck(pParse, SQLITE_DELETE, zTab, 0, zDb) ){
      return;
    }
  }
#endif

  /* Generate code to destroy the database record of the trigger.
  */
  if( pTable!=0 && !nested && (v = sqliteGetVdbe(pParse))!=0 ){

      { OP_Delete,     0, 0,        0},
      { OP_Next,       0, ADDR(3),  0}, /* 7 */
    };

    sqliteBeginWriteOperation(pParse, 0, 0);
    sqliteOpenMasterTable(v, pTrigger->iDb);
    base = sqliteVdbeAddOpList(v,  ArraySize(dropTrigger), dropTrigger);
    sqliteVdbeChangeP3(v, base+1, pTrigger->name, 0);
    if( pTrigger->iDb==0 ){
      sqliteChangeCookie(db, v);
    }
    sqliteVdbeAddOp(v, OP_Close, 0, 0);
    sqliteEndWriteOperation(pParse);
  }

  /*
   * If this is not an "explain", then delete the trigger structure.
   */
  if( !pParse->explain ){
    const char *zName = pTrigger->name;
    int nName = strlen(zName);
    if( pTable->pTrigger == pTrigger ){
      pTable->pTrigger = pTrigger->pNext;
    }else{
      Trigger *cc = pTable->pTrigger;
      while( cc ){ 
        if( cc->pNext == pTrigger ){
          cc->pNext = cc->pNext->pNext;
          break;
        }
        cc = cc->pNext;
      }
      assert(cc);
    }
    sqliteHashInsert(&(db->aDb[pTrigger->iDb].trigHash), zName, nName+1, 0);
    sqliteDeleteTrigger(pTrigger);
  }



}

/*
** pEList is the SET clause of an UPDATE statement.  Each entry
** in pEList is of the format <id>=<expr>.  If any of the entries
** in pEList have an <id> which matches an identifier in pIdList,
** then return TRUE.  If pIdList==NULL, then it is considered a

# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test trigger1-1.1.2 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: no_such_table}}
do_test trigger1-1.1.2 {
   catchsql {
     CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: no_such_table}}

execsql {
    CREATE TABLE t1(a);
}
execsql {

  db close
  sqlite db test.db
  execsql {
    INSERT INTO t1 VALUES(5,6);
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  }
} {1 2 3 4 5 6 3 4}

do_test trigger-4.1 {
  execsql {
    CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO t2 VALUES(NEW.a,NEW.b);
    END;
    INSERT INTO t1 VALUES(7,8);
    SELECT * FROM t2;
  }
} {3 4 7 8}
do_test trigger-4.2 {
  sqlite db2 test.db
  execsql {
    INSERT INTO t1 VALUES(9,10);
  } db2;
  db2 close
  execsql {
    SELECT * FROM t2;
  }
} {3 4 7 8}
do_test trigger-4.3 {
  execsql {
    DROP TABLE t1;
    SELECT * FROM t2;
  };
} {3 4 7 8}
do_test trigger-4.4 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM t2;
  };
} {3 4 7 8}


finish_test