/ Check-in [794bf67b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added FOR EACH ROW triggers functionality (CVS 562)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:794bf67b6b36fce8854d5daff12f21dbb943240c
User & Date: danielk1977 2002-05-15 08:30:13
Context
2002-05-15
08:43
Add the Makefile.in that was forgotten with checkin #562 (CVS 563) check-in: 29b8330c user: danielk1977 tags: trunk
08:30
Added FOR EACH ROW triggers functionality (CVS 562) check-in: 794bf67b user: danielk1977 tags: trunk
2002-05-10
14:41
Version 2.4.12 (CVS 561) check-in: 06cdaf1c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    21     21   **     COPY
    22     22   **     VACUUM
    23     23   **     BEGIN TRANSACTION
    24     24   **     COMMIT
    25     25   **     ROLLBACK
    26     26   **     PRAGMA
    27     27   **
    28         -** $Id: build.c,v 1.87 2002/05/08 21:30:15 drh Exp $
           28  +** $Id: build.c,v 1.88 2002/05/15 08:30:13 danielk1977 Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   #include <ctype.h>
    32     32   
    33     33   /*
    34     34   ** This routine is called after a single SQL statement has been
    35     35   ** parsed and we want to execute the VDBE code to implement 
................................................................................
   246    246       pIndex->isCommit = 1;
   247    247     }
   248    248     while( (pElem=sqliteHashFirst(&db->idxDrop))!=0 ){
   249    249       Index *pIndex = sqliteHashData(pElem);
   250    250       sqliteUnlinkAndDeleteIndex(db, pIndex);
   251    251     }
   252    252     sqliteHashClear(&db->idxDrop);
          253  +
          254  +  /* Set the commit flag on all triggers added this transaction */
          255  +  for(pElem=sqliteHashFirst(&db->trigHash); pElem; pElem=sqliteHashNext(pElem)){
          256  +    Trigger *pTrigger = sqliteHashData(pElem);
          257  +    pTrigger->isCommit = 1;
          258  +  }
          259  +
          260  +  /* Delete the structures for triggers removed this transaction */
          261  +  pElem = sqliteHashFirst(&db->trigDrop);
          262  +  while (pElem) {
          263  +    Trigger *pTrigger = sqliteHashData(pElem);
          264  +    sqliteDeleteTrigger(pTrigger);
          265  +    pElem = sqliteHashNext(pElem);
          266  +  }
          267  +  sqliteHashClear(&db->trigDrop);
          268  +
   253    269     db->flags &= ~SQLITE_InternChanges;
   254    270   }
   255    271   
   256    272   /*
   257    273   ** This routine runs when one or more CREATE TABLE, CREATE INDEX,
   258    274   ** DROP TABLE, or DROP INDEX statements gets rolled back.  The
   259    275   ** additions or deletions of Table and Index structures in the
................................................................................
   300    316       Index *pOld, *p = sqliteHashData(pElem);
   301    317       assert( p->isCommit );
   302    318       p->isDropped = 0;
   303    319       pOld = sqliteHashInsert(&db->idxHash, p->zName, strlen(p->zName)+1, p);
   304    320       assert( pOld==0 || pOld==p );
   305    321     }
   306    322     sqliteHashClear(&db->idxDrop);
          323  +
          324  +  /* Remove any triggers that haven't been commited yet */
          325  +  for(pElem = sqliteHashFirst(&db->trigHash); pElem; 
          326  +      pElem = (pElem?sqliteHashNext(pElem):0)) {
          327  +    Trigger * pTrigger = sqliteHashData(pElem);
          328  +    if (!pTrigger->isCommit) {
          329  +      Table * tbl = sqliteFindTable(db, pTrigger->table);
          330  +      if (tbl) {
          331  +	if (tbl->pTrigger == pTrigger) 
          332  +	  tbl->pTrigger = pTrigger->pNext;
          333  +	else {
          334  +	  Trigger * cc = tbl->pTrigger;
          335  +	  while (cc) {
          336  +	    if (cc->pNext == pTrigger) {
          337  +	      cc->pNext = cc->pNext->pNext;
          338  +	      break;
          339  +	    }
          340  +	    cc = cc->pNext;
          341  +	  }
          342  +	  assert(cc);
          343  +	}
          344  +      }
          345  +      sqliteHashInsert(&db->trigHash, pTrigger->name,
          346  +	      1 + strlen(pTrigger->name), 0);
          347  +      sqliteDeleteTrigger(pTrigger);
          348  +      pElem = sqliteHashFirst(&db->trigHash);
          349  +    }
          350  +  }
          351  +
          352  +  /* Any triggers that were dropped - put 'em back in place */
          353  +  for(pElem = sqliteHashFirst(&db->trigDrop); pElem; 
          354  +      pElem = sqliteHashNext(pElem)) {
          355  +    Trigger * pTrigger = sqliteHashData(pElem);
          356  +    Table * tab = sqliteFindTable(db, pTrigger->table);
          357  +    sqliteHashInsert(&db->trigHash, pTrigger->name, 
          358  +	strlen(pTrigger->name) + 1, pTrigger);
          359  +
          360  +    pTrigger->pNext = tab->pTrigger;
          361  +    tab->pTrigger = pTrigger;
          362  +  }
          363  +
          364  +  sqliteHashClear(&db->trigDrop);
   307    365     db->flags &= ~SQLITE_InternChanges;
   308    366   }
   309    367   
   310    368   /*
   311    369   ** Construct the name of a user table or index from a token.
   312    370   **
   313    371   ** Space to hold the name is obtained from sqliteMalloc() and must
................................................................................
   591    649   **
   592    650   ** This plan is not completely bullet-proof.  It is possible for
   593    651   ** the schema to change multiple times and for the cookie to be
   594    652   ** set back to prior value.  But schema changes are infrequent
   595    653   ** and the probability of hitting the same cookie value is only
   596    654   ** 1 chance in 2^32.  So we're safe enough.
   597    655   */
   598         -static void changeCookie(sqlite *db){
          656  +void changeCookie(sqlite *db){
   599    657     if( db->next_cookie==db->schema_cookie ){
   600    658       db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1;
   601    659       db->flags |= SQLITE_InternChanges;
   602    660     }
   603    661   }
   604    662   
   605    663   /*
................................................................................
  1032   1090         { OP_Next,       0, ADDR(4),  0}, /* 8 */
  1033   1091         { OP_Integer,    0, 0,        0}, /* 9 */
  1034   1092         { OP_SetCookie,  0, 0,        0},
  1035   1093         { OP_Close,      0, 0,        0},
  1036   1094       };
  1037   1095       Index *pIdx;
  1038   1096       sqliteBeginWriteOperation(pParse);
         1097  +    /* Drop all triggers associated with the table being dropped */
         1098  +    while (pTable->pTrigger) {
         1099  +      Token tt;
         1100  +      tt.z = pTable->pTrigger->name;
         1101  +      tt.n = strlen(pTable->pTrigger->name);
         1102  +      sqliteDropTrigger(pParse, &tt, 1);
         1103  +    }
  1039   1104       if( !pTable->isTemp ){
  1040   1105         base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable);
  1041   1106         sqliteVdbeChangeP3(v, base+2, pTable->zName, 0);
  1042   1107         changeCookie(db);
  1043   1108         sqliteVdbeChangeP1(v, base+9, db->next_cookie);
  1044   1109       }
  1045   1110       if( !isView ){
................................................................................
  1649   1714   ** all.  So there is not need to set a checkpoint is a transaction
  1650   1715   ** is already in effect.
  1651   1716   */
  1652   1717   void sqliteBeginWriteOperation(Parse *pParse){
  1653   1718     Vdbe *v;
  1654   1719     v = sqliteGetVdbe(pParse);
  1655   1720     if( v==0 ) return;
         1721  +  if (pParse->trigStack) return; /* if this is in a trigger */
  1656   1722     if( (pParse->db->flags & SQLITE_InTrans)==0  ){
  1657   1723       sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
  1658   1724       sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
  1659   1725       pParse->schemaVerified = 1;
  1660   1726     }
  1661   1727   }
  1662   1728   
................................................................................
  1668   1734   ** of a transaction, then this sets a checkpoint.  If we are not in
  1669   1735   ** a transaction, then start a transaction.
  1670   1736   */
  1671   1737   void sqliteBeginMultiWriteOperation(Parse *pParse){
  1672   1738     Vdbe *v;
  1673   1739     v = sqliteGetVdbe(pParse);
  1674   1740     if( v==0 ) return;
         1741  +  if (pParse->trigStack) return; /* if this is in a trigger */
  1675   1742     if( (pParse->db->flags & SQLITE_InTrans)==0 ){
  1676   1743       sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
  1677   1744       sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
  1678   1745       pParse->schemaVerified = 1;
  1679   1746     }else{
  1680   1747       sqliteVdbeAddOp(v, OP_Checkpoint, 0, 0);
  1681   1748     }
................................................................................
  1685   1752   ** Generate code that concludes an operation that may have changed
  1686   1753   ** the database.  This is a companion function to BeginWriteOperation().
  1687   1754   ** If a transaction was started, then commit it.  If a checkpoint was
  1688   1755   ** started then commit that.
  1689   1756   */
  1690   1757   void sqliteEndWriteOperation(Parse *pParse){
  1691   1758     Vdbe *v;
         1759  +  if (pParse->trigStack) return; /* if this is in a trigger */
  1692   1760     v = sqliteGetVdbe(pParse);
  1693   1761     if( v==0 ) return;
  1694   1762     if( pParse->db->flags & SQLITE_InTrans ){
  1695   1763       /* Do Nothing */
  1696   1764     }else{
  1697   1765       sqliteVdbeAddOp(v, OP_Commit, 0, 0);
  1698   1766     }
................................................................................
  1910   1978         int size = db->cache_size;
  1911   1979         if( size<0 ) size = -size;
  1912   1980         if( !getBoolean(zRight) ) size = -size;
  1913   1981         db->cache_size = size;
  1914   1982         sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
  1915   1983       }
  1916   1984     }else
         1985  +
         1986  +  if( sqliteStrICmp(zLeft, "trigger_overhead_test")==0 ){
         1987  +    if( getBoolean(zRight) ){
         1988  +      always_code_trigger_setup = 1;
         1989  +    }else{
         1990  +      always_code_trigger_setup = 0;
         1991  +    }
         1992  +  }else
  1917   1993   
  1918   1994     if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){
  1919   1995       if( getBoolean(zRight) ){
  1920   1996         db->flags |= SQLITE_VdbeTrace;
  1921   1997       }else{
  1922   1998         db->flags &= ~SQLITE_VdbeTrace;
  1923   1999       }

Changes to src/delete.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle DELETE FROM statements.
    14     14   **
    15         -** $Id: delete.c,v 1.30 2002/04/12 10:08:59 drh Exp $
           15  +** $Id: delete.c,v 1.31 2002/05/15 08:30:13 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Given a table name, find the corresponding table and make sure the
    22     22   ** table is writeable.  Generate an error and return NULL if not.  If
................................................................................
    80     80     int i;                 /* Loop counter */
    81     81     WhereInfo *pWInfo;     /* Information about the WHERE clause */
    82     82     Index *pIdx;           /* For looping over indices of the table */
    83     83     int base;              /* Index of the first available table cursor */
    84     84     sqlite *db;            /* Main database structure */
    85     85     int openOp;            /* Opcode used to open a cursor to the table */
    86     86   
           87  +  int row_triggers_exist = 0;
           88  +  int oldIdx = -1;
    87     89   
    88     90     if( pParse->nErr || sqlite_malloc_failed ){
    89     91       pTabList = 0;
    90     92       goto delete_from_cleanup;
    91     93     }
    92     94     db = pParse->db;
           95  +
           96  +  /* Check for the special case of a VIEW with one or more ON DELETE triggers 
           97  +   * defined 
           98  +   */
           99  +  {
          100  +    Table * pTab;
          101  +    char * zTab = sqliteTableNameFromToken(pTableName);
          102  +
          103  +    if(zTab != 0) {
          104  +      pTab = sqliteFindTable(pParse->db, zTab);
          105  +      if (pTab) {
          106  +	row_triggers_exist = 
          107  +	  sqliteTriggersExist(pParse, pTab->pTrigger, 
          108  +	      TK_DELETE, TK_BEFORE, TK_ROW, 0) ||
          109  +	  sqliteTriggersExist(pParse, pTab->pTrigger, 
          110  +	      TK_DELETE, TK_AFTER, TK_ROW, 0);
          111  +      }
          112  +      sqliteFree(zTab);
          113  +      if (row_triggers_exist &&  pTab->pSelect ) {
          114  +	/* Just fire VIEW triggers */
          115  +	sqliteViewTriggers(pParse, pTab, pWhere, OE_Replace, 0);
          116  +	return;
          117  +      }
          118  +    }
          119  +  }
    93    120   
    94    121     /* Locate the table which we want to delete.  This table has to be
    95    122     ** put in an IdList structure because some of the subroutines we
    96    123     ** will be calling are designed to work with multiple tables and expect
    97    124     ** an IdList* parameter instead of just a Table* parameger.
    98    125     */
    99    126     pTabList = sqliteTableTokenToIdList(pParse, pTableName);
   100    127     if( pTabList==0 ) goto delete_from_cleanup;
   101    128     assert( pTabList->nId==1 );
   102    129     pTab = pTabList->a[0].pTab;
   103    130     assert( pTab->pSelect==0 );  /* This table is not a view */
   104    131   
          132  +  if (row_triggers_exist) 
          133  +    oldIdx = pParse->nTab++;
          134  +
   105    135     /* Resolve the column names in all the expressions.
   106    136     */
   107    137     base = pParse->nTab++;
   108    138     if( pWhere ){
   109    139       if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
   110    140         goto delete_from_cleanup;
   111    141       }
................................................................................
   114    144       }
   115    145     }
   116    146   
   117    147     /* Begin generating code.
   118    148     */
   119    149     v = sqliteGetVdbe(pParse);
   120    150     if( v==0 ) goto delete_from_cleanup;
   121         -  sqliteBeginWriteOperation(pParse);
          151  +  if (row_triggers_exist) 
          152  +    sqliteBeginMultiWriteOperation(pParse);
          153  +  else 
          154  +    sqliteBeginWriteOperation(pParse);
   122    155   
   123    156     /* Initialize the counter of the number of rows deleted, if
   124    157     ** we are counting rows.
   125    158     */
   126    159     if( db->flags & SQLITE_CountRows ){
   127    160       sqliteVdbeAddOp(v, OP_Integer, 0, 0);
   128    161     }
   129    162   
   130    163     /* Special case: A DELETE without a WHERE clause deletes everything.
   131    164     ** It is easier just to erase the whole table.
   132    165     */
   133         -  if( pWhere==0 ){
          166  +  if( pWhere==0 && !row_triggers_exist){
   134    167       if( db->flags & SQLITE_CountRows ){
   135    168         /* If counting rows deleted, just count the total number of
   136    169         ** entries in the table. */
   137    170         int endOfLoop = sqliteVdbeMakeLabel(v);
   138    171         int addr;
   139    172         openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
   140    173         assert( base==0 );
................................................................................
   172    205       sqliteWhereEnd(pWInfo);
   173    206   
   174    207       /* Delete every item whose key was written to the list during the
   175    208       ** database scan.  We have to delete items after the scan is complete
   176    209       ** because deleting an item can change the scan order.
   177    210       */
   178    211       sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
          212  +    end = sqliteVdbeMakeLabel(v);
          213  +
          214  +    if (row_triggers_exist) {
          215  +      int ii;
          216  +      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
          217  +      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
          218  +
          219  +      openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
          220  +      sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
          221  +      sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
          222  +      sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0);
          223  +
          224  +      sqliteVdbeAddOp(v, OP_Integer, 13, 0);
          225  +      for (ii = 0; ii < pTab->nCol; ii++) {
          226  +	if (ii == pTab->iPKey) 
          227  +	  sqliteVdbeAddOp(v, OP_Recno, base, 0);
          228  +	else
          229  +	  sqliteVdbeAddOp(v, OP_Column, base, ii);
          230  +      }
          231  +      sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
          232  +      sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
          233  +      sqliteVdbeAddOp(v, OP_Close, base, 0);
          234  +      sqliteVdbeAddOp(v, OP_Rewind, oldIdx, 0);
          235  +
          236  +      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
          237  +	  oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default);
          238  +    }
          239  +
          240  +    pParse->nTab = base + 1;
   179    241       openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
   180    242       sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
   181    243       for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   182         -      sqliteVdbeAddOp(v, openOp, base+i, pIdx->tnum);
          244  +      sqliteVdbeAddOp(v, openOp, pParse->nTab++, pIdx->tnum);
          245  +    }
          246  +
          247  +    if (!row_triggers_exist) 
          248  +      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
          249  +
          250  +    sqliteGenerateRowDelete(v, pTab, base, pParse->trigStack?0:1);
          251  +
          252  +    if (row_triggers_exist) {
          253  +      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          254  +	sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum);
          255  +      }
          256  +      sqliteVdbeAddOp(v, OP_Close, base, 0);
          257  +      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
          258  +	  oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default);
   183    259       }
   184         -    end = sqliteVdbeMakeLabel(v);
   185         -    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
   186         -    sqliteGenerateRowDelete(v, pTab, base, 1);
          260  +
   187    261       sqliteVdbeAddOp(v, OP_Goto, 0, addr);
   188    262       sqliteVdbeResolveLabel(v, end);
   189    263       sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
          264  +
          265  +    if (!row_triggers_exist) {
          266  +      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          267  +	sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum);
          268  +      }
          269  +      sqliteVdbeAddOp(v, OP_Close, base, 0);
          270  +      pParse->nTab = base;
          271  +    }
   190    272     }
   191    273     sqliteEndWriteOperation(pParse);
   192    274   
   193    275     /*
   194    276     ** Return the number of rows that were deleted.
   195    277     */
   196    278     if( db->flags & SQLITE_CountRows ){

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.58 2002/04/20 14:24:42 drh Exp $
           15  +** $Id: expr.c,v 1.59 2002/05/15 08:30:13 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Construct a new expression node and return a pointer to it.  Memory
    22     22   ** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
   477    477                 pExpr->iColumn = -1;
   478    478               }else{
   479    479                 pExpr->iColumn = j;
   480    480               }
   481    481             }
   482    482           }
   483    483         }
          484  +
          485  +      /* If we have not already resolved this *.* expression, then maybe 
          486  +       * it is a new.* or old.* trigger argument reference */
          487  +      if (cnt == 0 && pParse->trigStack != 0) {
          488  +        TriggerStack * tt = pParse->trigStack;
          489  +        int j;
          490  +        int t = 0;
          491  +        if (tt->newIdx != -1 && sqliteStrICmp("new", zLeft) == 0) {
          492  +          pExpr->iTable = tt->newIdx;
          493  +          cntTab++;
          494  +          t = 1;
          495  +        }
          496  +        if (tt->oldIdx != -1 && sqliteStrICmp("old", zLeft) == 0) {
          497  +          pExpr->iTable = tt->oldIdx;
          498  +          cntTab++;
          499  +          t = 1;
          500  +        }
          501  +
          502  +        if (t) 
          503  +          for(j=0; j<tt->pTab->nCol; j++) {
          504  +            if( sqliteStrICmp(tt->pTab->aCol[j].zName, zRight)==0 ){
          505  +              cnt++;
          506  +              pExpr->iColumn = j;
          507  +            }
          508  +          }
          509  +      }
          510  +
   484    511         if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
   485    512           cnt = 1;
   486    513           pExpr->iColumn = -1;
   487    514         }
   488    515         sqliteFree(zLeft);
   489    516         sqliteFree(zRight);
   490    517         if( cnt==0 ){

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.52 2002/04/12 10:08:59 drh Exp $
           15  +** $Id: insert.c,v 1.53 2002/05/15 08:30:13 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** This routine is call to handle SQL of the following forms:
    21     21   **
    22     22   **    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    36     36     Token *pTableName,    /* Name of table into which we are inserting */
    37     37     ExprList *pList,      /* List of values to be inserted */
    38     38     Select *pSelect,      /* A SELECT statement to use as the data source */
    39     39     IdList *pColumn,      /* Column names corresponding to IDLIST. */
    40     40     int onError           /* How to handle constraint errors */
    41     41   ){
    42     42     Table *pTab;          /* The table to insert into */
    43         -  char *zTab;           /* Name of the table into which we are inserting */
           43  +  char *zTab = 0;       /* Name of the table into which we are inserting */
    44     44     int i, j, idx;        /* Loop counters */
    45     45     Vdbe *v;              /* Generate code into this virtual machine */
    46     46     Index *pIdx;          /* For looping over indices of the table */
    47     47     int srcTab;           /* Date comes from this temporary cursor if >=0 */
    48     48     int nColumn;          /* Number of columns in the data */
    49     49     int base;             /* First available cursor */
    50     50     int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
    51     51     sqlite *db;           /* The main database structure */
    52     52     int openOp;           /* Opcode used to open cursors */
    53     53     int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
    54     54     int endOfLoop;        /* Label for the end of the insertion loop */
           55  +
           56  +  int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
           57  +  int newIdx = -1;
    55     58   
    56     59     if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
    57     60     db = pParse->db;
    58     61   
    59     62     /* Locate the table into which we will be inserting new information.
    60     63     */
    61     64     zTab = sqliteTableNameFromToken(pTableName);
    62     65     if( zTab==0 ) goto insert_cleanup;
    63         -  pTab = sqliteTableNameToTable(pParse, zTab);
           66  +  pTab = sqliteFindTable(pParse->db, zTab);
           67  +  if( pTab==0 ){
           68  +    sqliteSetString(&pParse->zErrMsg, "no such table: ", zTab, 0);
           69  +    pParse->nErr++;
           70  +    goto insert_cleanup;
           71  +  }
           72  +
           73  +  /* Ensure that:
           74  +  *  (a) the table is not read-only, 
           75  +  *  (b) that if it is a view then ON INSERT triggers exist
           76  +  */
           77  +  row_triggers_exist = 
           78  +    sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, 
           79  +	TK_BEFORE, TK_ROW, 0) ||
           80  +    sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, TK_AFTER, TK_ROW, 0);
           81  +  if( pTab->readOnly || (pTab->pSelect && !row_triggers_exist) ){
           82  +    sqliteSetString(&pParse->zErrMsg, 
           83  +      pTab->pSelect ? "view " : "table ",
           84  +      zTab,
           85  +      " may not be modified", 0);
           86  +    pParse->nErr++;
           87  +    goto insert_cleanup;
           88  +  }
    64     89     sqliteFree(zTab);
           90  +  zTab = 0;
           91  +
    65     92     if( pTab==0 ) goto insert_cleanup;
    66         -  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
    67     93   
    68     94     /* Allocate a VDBE
    69     95     */
    70     96     v = sqliteGetVdbe(pParse);
    71     97     if( v==0 ) goto insert_cleanup;
    72         -  if( pSelect ){
           98  +  if( pSelect || row_triggers_exist ){
    73     99       sqliteBeginMultiWriteOperation(pParse);
    74    100     }else{
    75    101       sqliteBeginWriteOperation(pParse);
    76    102     }
          103  +
          104  +  /* if there are row triggers, allocate a temp table for new.* references. */
          105  +  if (row_triggers_exist)
          106  +    newIdx = pParse->nTab++;
    77    107   
    78    108     /* Figure out how many columns of data are supplied.  If the data
    79    109     ** is coming from a SELECT statement, then this step has to generate
    80    110     ** all the code to implement the SELECT statement and leave the data
    81    111     ** in a temporary table.  If data is coming from an expression list,
    82    112     ** then we just have to count the number of expressions.
    83    113     */
................................................................................
   169    199     ** key, the set the keyColumn variable to the primary key column index
   170    200     ** in the original table definition.
   171    201     */
   172    202     if( pColumn==0 ){
   173    203       keyColumn = pTab->iPKey;
   174    204     }
   175    205   
   176         -  /* Open cursors into the table that is received the new data and
   177         -  ** all indices of that table.
   178         -  */
   179         -  base = pParse->nTab;
   180         -  openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
   181         -  sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
   182         -  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
   183         -  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
   184         -    sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum);
   185         -    sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
   186         -  }
   187         -  pParse->nTab += idx;
   188         -
          206  +  /* Open the temp table for FOR EACH ROW triggers */
          207  +  if (row_triggers_exist)
          208  +    sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
          209  +    
   189    210     /* Initialize the count of rows to be inserted
   190    211     */
   191         -  if( db->flags & SQLITE_CountRows ){
          212  +  if( db->flags & SQLITE_CountRows && !pParse->trigStack){
   192    213       sqliteVdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
   193    214     }
          215  +
          216  +  /* Open tables and indices if there are no row triggers */
          217  +  if (!row_triggers_exist) {
          218  +    base = pParse->nTab;
          219  +    openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
          220  +    sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
          221  +    sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
          222  +    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
          223  +      sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum);
          224  +      sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
          225  +    }
          226  +    pParse->nTab += idx;
          227  +  }
   194    228   
   195    229     /* If the data source is a SELECT statement, then we have to create
   196    230     ** a loop because there might be multiple rows of data.  If the data
   197    231     ** source is an expression list, then exactly one row will be inserted
   198    232     ** and the loop is not used.
   199    233     */
   200    234     if( srcTab>=0 ){
   201    235       iBreak = sqliteVdbeMakeLabel(v);
   202    236       sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
   203    237       iCont = sqliteVdbeCurrentAddr(v);
   204    238     }
          239  +
          240  +  if (row_triggers_exist) {
          241  +
          242  +    /* build the new.* reference row */
          243  +    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
          244  +    for(i=0; i<pTab->nCol; i++){
          245  +      if( pColumn==0 ){
          246  +	j = i;
          247  +      }else{
          248  +	for(j=0; j<pColumn->nId; j++){
          249  +	  if( pColumn->a[j].idx==i ) break;
          250  +	}
          251  +      }
          252  +      if( pColumn && j>=pColumn->nId ){
          253  +	sqliteVdbeAddOp(v, OP_String, 0, 0);
          254  +	sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
          255  +      }else if( srcTab>=0 ){
          256  +	sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
          257  +      }else{
          258  +	sqliteExprCode(pParse, pList->a[j].pExpr);
          259  +      }
          260  +    }
          261  +    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
          262  +    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
          263  +    sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);
          264  +
          265  +    /* Fire BEFORE triggers */
          266  +    if (
          267  +    sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab, newIdx, -1, 
          268  +	onError)
          269  +       ) goto insert_cleanup;
          270  +
          271  +    /* Open the tables and indices for the INSERT */
          272  +    if (!pTab->pSelect) {
          273  +      base = pParse->nTab;
          274  +      openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
          275  +      sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
          276  +      sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
          277  +      for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
          278  +	sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum);
          279  +	sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
          280  +      }
          281  +      pParse->nTab += idx;
          282  +    }
          283  +  }
   205    284   
   206    285     /* Push the record number for the new entry onto the stack.  The
   207    286     ** record number is a randomly generate integer created by NewRecno
   208    287     ** except when the table has an INTEGER PRIMARY KEY column, in which
   209    288     ** case the record number is the same as that column. 
   210    289     */
   211         -  if( keyColumn>=0 ){
   212         -    if( srcTab>=0 ){
   213         -      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
          290  +  if (!pTab->pSelect) {
          291  +    if( keyColumn>=0 ){
          292  +      if( srcTab>=0 ){
          293  +	sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
          294  +      }else{
          295  +	int addr;
          296  +	sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
          297  +
          298  +	/* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
          299  +	 ** to generate a unique primary key value.
          300  +	 */
          301  +	addr = sqliteVdbeAddOp(v, OP_Dup, 0, 1);
          302  +	sqliteVdbeAddOp(v, OP_NotNull, 0, addr+4);
          303  +	sqliteVdbeAddOp(v, OP_Pop, 1, 0);
          304  +	sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
          305  +      }
          306  +      sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
   214    307       }else{
   215         -      int addr;
   216         -      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
   217         -
   218         -      /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
   219         -      ** to generate a unique primary key value.
   220         -      */
   221         -      addr = sqliteVdbeAddOp(v, OP_Dup, 0, 1);
   222         -      sqliteVdbeAddOp(v, OP_NotNull, 0, addr+4);
   223         -      sqliteVdbeAddOp(v, OP_Pop, 1, 0);
   224    308         sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
   225    309       }
   226         -    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
   227         -  }else{
   228         -    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
   229         -  }
   230         -
   231         -  /* Push onto the stack, data for all columns of the new entry, beginning
   232         -  ** with the first column.
   233         -  */
   234         -  for(i=0; i<pTab->nCol; i++){
   235         -    if( i==pTab->iPKey ){
   236         -      /* The value of the INTEGER PRIMARY KEY column is always a NULL.
   237         -      ** Whenever this column is read, the record number will be substituted
   238         -      ** in its place.  So will fill this column with a NULL to avoid
   239         -      ** taking up data space with information that will never be used. */
   240         -      sqliteVdbeAddOp(v, OP_String, 0, 0);
   241         -      continue;
   242         -    }
   243         -    if( pColumn==0 ){
   244         -      j = i;
   245         -    }else{
   246         -      for(j=0; j<pColumn->nId; j++){
   247         -        if( pColumn->a[j].idx==i ) break;
          310  +
          311  +    /* Push onto the stack, data for all columns of the new entry, beginning
          312  +     ** with the first column.
          313  +     */
          314  +    for(i=0; i<pTab->nCol; i++){
          315  +      if( i==pTab->iPKey ){
          316  +	/* The value of the INTEGER PRIMARY KEY column is always a NULL.
          317  +	 ** Whenever this column is read, the record number will be substituted
          318  +	 ** in its place.  So will fill this column with a NULL to avoid
          319  +	 ** taking up data space with information that will never be used. */
          320  +	sqliteVdbeAddOp(v, OP_String, 0, 0);
          321  +	continue;
          322  +      }
          323  +      if( pColumn==0 ){
          324  +	j = i;
          325  +      }else{
          326  +	for(j=0; j<pColumn->nId; j++){
          327  +	  if( pColumn->a[j].idx==i ) break;
          328  +	}
          329  +      }
          330  +      if( pColumn && j>=pColumn->nId ){
          331  +	sqliteVdbeAddOp(v, OP_String, 0, 0);
          332  +	sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
          333  +      }else if( srcTab>=0 ){
          334  +	sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
          335  +      }else{
          336  +	sqliteExprCode(pParse, pList->a[j].pExpr);
   248    337         }
   249    338       }
   250         -    if( pColumn && j>=pColumn->nId ){
   251         -      sqliteVdbeAddOp(v, OP_String, 0, 0);
   252         -      sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
   253         -    }else if( srcTab>=0 ){
   254         -      sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
   255         -    }else{
   256         -      sqliteExprCode(pParse, pList->a[j].pExpr);
          339  +
          340  +    /* Generate code to check constraints and generate index keys and
          341  +     ** do the insertion.
          342  +     */
          343  +    endOfLoop = sqliteVdbeMakeLabel(v);
          344  +    sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0,onError,endOfLoop);
          345  +    sqliteCompleteInsertion(pParse, pTab, base, 0,0,0);
          346  +
          347  +    /* Update the count of rows that are inserted
          348  +     */
          349  +    if( (db->flags & SQLITE_CountRows)!=0 && !pParse->trigStack){
          350  +      sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
   257    351       }
   258    352     }
   259    353   
   260         -  /* Generate code to check constraints and generate index keys and
   261         -  ** do the insertion.
   262         -  */
   263         -  endOfLoop = sqliteVdbeMakeLabel(v);
   264         -  sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0, onError, endOfLoop);
   265         -  sqliteCompleteInsertion(pParse, pTab, base, 0,0,0);
          354  +  if (row_triggers_exist) {
          355  +    /* Close all tables opened */
          356  +    if (!pTab->pSelect) {
          357  +      sqliteVdbeAddOp(v, OP_Close, base, 0);
          358  +      for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
          359  +	sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
          360  +      }
          361  +    }
   266    362   
   267         -  /* Update the count of rows that are inserted
   268         -  */
   269         -  if( (db->flags & SQLITE_CountRows)!=0 ){
   270         -    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
          363  +    /* Code AFTER triggers */
          364  +    if (
          365  +	sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1, 
          366  +	  onError)
          367  +       ) goto insert_cleanup;
   271    368     }
   272    369   
   273    370     /* The bottom of the loop, if the data source is a SELECT statement
   274         -  */
          371  +   */
   275    372     sqliteVdbeResolveLabel(v, endOfLoop);
   276    373     if( srcTab>=0 ){
   277    374       sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
   278    375       sqliteVdbeResolveLabel(v, iBreak);
   279    376       sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
   280    377     }
   281         -  sqliteVdbeAddOp(v, OP_Close, base, 0);
   282         -  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
   283         -    sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
          378  +
          379  +  if (!row_triggers_exist) {
          380  +    /* Close all tables opened */
          381  +    sqliteVdbeAddOp(v, OP_Close, base, 0);
          382  +    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
          383  +      sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
          384  +    }
   284    385     }
          386  +
   285    387     sqliteEndWriteOperation(pParse);
   286    388   
   287    389     /*
   288         -  ** Return the number of rows inserted.
          390  +   ** Return the number of rows inserted.
   289    391     */
   290         -  if( db->flags & SQLITE_CountRows ){
          392  +  if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
   291    393       sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
   292    394       sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
   293    395       sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC);
   294    396       sqliteVdbeAddOp(v, OP_Callback, 1, 0);
   295    397     }
   296    398   
   297    399   insert_cleanup:
   298    400     if( pList ) sqliteExprListDelete(pList);
   299    401     if( pSelect ) sqliteSelectDelete(pSelect);
          402  +  if ( zTab ) sqliteFree(zTab);
   300    403     sqliteIdListDelete(pColumn);
   301    404   }
   302    405   
   303    406   /*
   304    407   ** Generate code to do a constraint check prior to an INSERT or an UPDATE.
   305    408   **
   306    409   ** When this routine is called, the stack contains (from bottom to top)
................................................................................
   569    672     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
   570    673     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
   571    674     for(i=nIdx-1; i>=0; i--){
   572    675       if( aIdxUsed && aIdxUsed[i]==0 ) continue;
   573    676       sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
   574    677     }
   575    678     sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
   576         -  sqliteVdbeAddOp(v, OP_PutIntKey, base, 1);
          679  +  sqliteVdbeAddOp(v, OP_PutIntKey, base, pParse->trigStack?0:1);
   577    680     if( isUpdate && recnoChng ){
   578    681       sqliteVdbeAddOp(v, OP_Pop, 1, 0);
   579    682     }
   580    683   }

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.71 2002/05/10 13:14:07 drh Exp $
           17  +** $Id: main.c,v 1.72 2002/05/15 08:30:13 danielk1977 Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   
    22     22   /*
    23     23   ** This is the callback routine for the code that initializes the
    24     24   ** database.  See sqliteInit() below for additional information.
................................................................................
   322    322   
   323    323     /* Allocate the sqlite data structure */
   324    324     db = sqliteMalloc( sizeof(sqlite) );
   325    325     if( pzErrMsg ) *pzErrMsg = 0;
   326    326     if( db==0 ) goto no_mem_on_open;
   327    327     sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
   328    328     sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0);
          329  +  sqliteHashInit(&db->trigHash, SQLITE_HASH_STRING, 0);
          330  +  sqliteHashInit(&db->trigDrop, SQLITE_HASH_STRING, 0);
   329    331     sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0);
   330    332     sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0);
   331    333     sqliteHashInit(&db->aFunc, SQLITE_HASH_STRING, 1);
   332    334     sqliteRegisterBuildinFunctions(db);
   333    335     db->onError = OE_Default;
   334    336     db->priorNewRowid = 0;
   335    337     db->magic = SQLITE_MAGIC_BUSY;
................................................................................
   379    381   ** This routine erases the stored schema.  This erasure occurs because
   380    382   ** either the database is being closed or because some other process
   381    383   ** changed the schema and this process needs to reread it.
   382    384   */
   383    385   static void clearHashTable(sqlite *db, int preserveTemps){
   384    386     HashElem *pElem;
   385    387     Hash temp1;
          388  +  Hash temp2;
   386    389     assert( sqliteHashFirst(&db->tblDrop)==0 ); /* There can not be uncommitted */
   387    390     assert( sqliteHashFirst(&db->idxDrop)==0 ); /*   DROP TABLEs or DROP INDEXs */
   388    391     temp1 = db->tblHash;
   389         -  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
          392  +  temp2 = db->trigHash;
          393  +  sqliteHashInit(&db->trigHash, SQLITE_HASH_STRING, 0);
   390    394     sqliteHashClear(&db->idxHash);
          395  +
          396  +  for (pElem=sqliteHashFirst(&temp2); pElem; pElem=sqliteHashNext(pElem)){
          397  +    Trigger * pTrigger = sqliteHashData(pElem);
          398  +    Table *pTab = sqliteFindTable(db, pTrigger->table);
          399  +    assert(pTab);
          400  +    if (pTab->isTemp) { 
          401  +      sqliteHashInsert(&db->trigHash, pTrigger->name, strlen(pTrigger->name), 
          402  +	  pTrigger);
          403  +    } else {
          404  +      sqliteDeleteTrigger(pTrigger);
          405  +    }
          406  +  }
          407  +  sqliteHashClear(&temp2);
          408  +
          409  +  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
          410  +
   391    411     for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){
   392    412       Table *pTab = sqliteHashData(pElem);
   393    413       if( preserveTemps && pTab->isTemp ){
   394    414         Index *pIdx;
   395    415         int nName = strlen(pTab->zName);
   396    416         Table *pOld = sqliteHashInsert(&db->tblHash, pTab->zName, nName+1, pTab);
   397    417         if( pOld!=0 ){
................................................................................
   409    429           }
   410    430         }
   411    431       }else{
   412    432         sqliteDeleteTable(db, pTab);
   413    433       }
   414    434     }
   415    435     sqliteHashClear(&temp1);
          436  +
   416    437     db->flags &= ~SQLITE_Initialized;
   417    438   }
   418    439   
   419    440   /*
   420    441   ** Return the ROWID of the most recent insert
   421    442   */
   422    443   int sqlite_last_insert_rowid(sqlite *db){
................................................................................
   454    475   }
   455    476   
   456    477   /*
   457    478   ** Return TRUE if the given SQL string ends in a semicolon.
   458    479   */
   459    480   int sqlite_complete(const char *zSql){
   460    481     int isComplete = 0;
          482  +  int seenCreate = 0;
   461    483     while( *zSql ){
   462    484       switch( *zSql ){
   463    485         case ';': {
   464    486           isComplete = 1;
   465    487           break;
   466    488         }
   467    489         case ' ':
................................................................................
   497    519             break;
   498    520           }
   499    521           while( *zSql && *zSql!='\n' ){ zSql++; }
   500    522           if( *zSql==0 ) return isComplete;
   501    523           break;
   502    524         } 
   503    525         default: {
          526  +        if (seenCreate && !sqliteStrNICmp(zSql, "trigger", 7)) 
          527  +	  while (sqliteStrNICmp(zSql, "end", 3))
          528  +	    if (!*++zSql) return 0;
          529  +
          530  +        if (!sqliteStrNICmp(zSql, "create", 6)) {
          531  +	  zSql = zSql + 5;
          532  +	  seenCreate = 1;
          533  +	} else 
          534  +	  seenCreate = 0;
          535  +
   504    536           isComplete = 0;
   505    537           break;
   506    538         }
   507    539       }
   508    540       zSql++;
   509    541     }
   510    542     return isComplete;

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.63 2002/05/08 21:46:15 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.64 2002/05/15 08:30:14 danielk1977 Exp $
    18     18   */
    19     19   %token_prefix TK_
    20     20   %token_type {Token}
    21     21   %default_type {Token}
    22     22   %extra_argument {Parse *pParse}
    23     23   %syntax_error {
    24     24     sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
    29     29   #include "sqliteInt.h"
    30     30   #include "parse.h"
    31     31   
    32     32   /*
    33     33   ** A structure for holding two integers
    34     34   */
    35     35   struct twoint { int a,b; };
           36  +
           37  +/*
           38  +** A structure for holding an integer and an IdList
           39  +*/
           40  +struct int_idlist { int a; IdList * b; };
    36     41   }
    37     42   
    38     43   // These are extra tokens used by the lexer but never seen by the
    39     44   // parser.  We put them in a rule so that the parser generator will
    40     45   // add them to the parse.h output file.
    41     46   //
    42     47   %nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
................................................................................
   624    629   cmd ::= PRAGMA ids(X).                   {sqlitePragma(pParse,&X,&X,0);}
   625    630   plus_num(A) ::= plus_opt number(X).   {A = X;}
   626    631   minus_num(A) ::= MINUS number(X).     {A = X;}
   627    632   number(A) ::= INTEGER(X).  {A = X;}
   628    633   number(A) ::= FLOAT(X).    {A = X;}
   629    634   plus_opt ::= PLUS.
   630    635   plus_opt ::= .
          636  +
          637  +//////////////////////////// The CREATE TRIGGER command /////////////////////
          638  +cmd ::= CREATE(A) TRIGGER ids(B) trigger_time(C) trigger_event(D) ON ids(E) 
          639  +                  foreach_clause(F) when_clause(G)
          640  +                  BEGIN trigger_cmd_list(S) END(Z). {
          641  +  sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, 
          642  +      A.z, (int)(Z.z - A.z) + Z.n );
          643  +}
          644  +
          645  +%type trigger_time  {int}
          646  +trigger_time(A) ::= BEFORE.      { A = TK_BEFORE; }
          647  +trigger_time(A) ::= AFTER.       { A = TK_AFTER;  }
          648  +trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
          649  +trigger_time(A) ::= .            { A = TK_BEFORE; }
          650  +
          651  +%type trigger_event {struct int_idlist}
          652  +trigger_event(A) ::= DELETE. { A.a = TK_DELETE; A.b = 0; }
          653  +trigger_event(A) ::= INSERT. { A.a = TK_INSERT; A.b = 0; }
          654  +trigger_event(A) ::= UPDATE. { A.a = TK_UPDATE; A.b = 0;}
          655  +trigger_event(A) ::= UPDATE OF inscollist(X). {A.a = TK_UPDATE; A.b = X; }
          656  +
          657  +%type foreach_clause {int}
          658  +foreach_clause(A) ::= .                   { A = TK_ROW; }
          659  +foreach_clause(A) ::= FOR EACH ROW.       { A = TK_ROW; }
          660  +foreach_clause(A) ::= FOR EACH STATEMENT. { A = TK_STATEMENT; }
          661  +
          662  +%type when_clause {Expr *}
          663  +when_clause(A) ::= .             { A = 0; }
          664  +when_clause(A) ::= WHEN expr(X). { A = X; }
          665  +
          666  +%type trigger_cmd_list {TriggerStep *}
          667  +trigger_cmd_list(A) ::= trigger_cmd(X) SEMI trigger_cmd_list(Y). {
          668  +  X->pNext = Y ; A = X; }
          669  +trigger_cmd_list(A) ::= . { A = 0; }
          670  +
          671  +%type trigger_cmd {TriggerStep *}
          672  +// UPDATE 
          673  +trigger_cmd(A) ::= UPDATE orconf(R) ids(X) SET setlist(Y) where_opt(Z).  
          674  +               { A = sqliteTriggerUpdateStep(&X, Y, Z, R); }
          675  +
          676  +// INSERT
          677  +trigger_cmd(A) ::= INSERT orconf(R) INTO ids(X) inscollist_opt(F) 
          678  +  VALUES LP itemlist(Y) RP.  
          679  +{A = sqliteTriggerInsertStep(&X, F, Y, 0, R);}
          680  +
          681  +trigger_cmd(A) ::= INSERT orconf(R) INTO ids(X) inscollist_opt(F) select(S).
          682  +               {A = sqliteTriggerInsertStep(&X, F, 0, S, R);}
          683  +
          684  +// DELETE
          685  +trigger_cmd(A) ::= DELETE FROM ids(X) where_opt(Y).
          686  +               {A = sqliteTriggerDeleteStep(&X, Y);}
          687  +
          688  +// SELECT
          689  +trigger_cmd(A) ::= select(X).  {A = sqliteTriggerSelectStep(X); }
          690  +
          691  +////////////////////////  DROP TRIGGER statement //////////////////////////////
          692  +cmd ::= DROP TRIGGER ids(X). {
          693  +    sqliteDropTrigger(pParse,&X,0);
          694  +}
          695  +

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.107 2002/05/10 13:14:07 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.108 2002/05/15 08:30:14 danielk1977 Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
   140    140   typedef struct Token Token;
   141    141   typedef struct IdList IdList;
   142    142   typedef struct WhereInfo WhereInfo;
   143    143   typedef struct WhereLevel WhereLevel;
   144    144   typedef struct Select Select;
   145    145   typedef struct AggExpr AggExpr;
   146    146   typedef struct FuncDef FuncDef;
          147  +typedef struct Trigger Trigger;
          148  +typedef struct TriggerStep TriggerStep;
          149  +typedef struct TriggerStack TriggerStack;
   147    150   
   148    151   /*
   149    152   ** Each database is an instance of the following structure
   150    153   */
   151    154   struct sqlite {
   152    155     Btree *pBe;                   /* The B*Tree backend */
   153    156     Btree *pBeTemp;               /* Backend for session temporary tables */
................................................................................
   166    169     Hash aFunc;                   /* All functions that can be in SQL exprs */
   167    170     int lastRowid;                /* ROWID of most recent insert */
   168    171     int priorNewRowid;            /* Last randomly generated ROWID */
   169    172     int onError;                  /* Default conflict algorithm */
   170    173     int magic;                    /* Magic number for detect library misuse */
   171    174     int nChange;                  /* Number of rows changed */
   172    175     int recursionDepth;           /* Number of nested calls to sqlite_exec() */
          176  +
          177  +  Hash trigHash;                /* All triggers indexed by name */
          178  +  Hash trigDrop;                /* Uncommited dropped triggers */
   173    179   };
   174    180   
   175    181   /*
   176    182   ** Possible values for the sqlite.flags.
   177    183   */
   178    184   #define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */
   179    185   #define SQLITE_Initialized    0x00000002  /* True after initialization */
................................................................................
   266    272     Select *pSelect; /* NULL for tables.  Points to definition if a view. */
   267    273     u8 readOnly;     /* True if this table should not be written by the user */
   268    274     u8 isCommit;     /* True if creation of this table has been committed */
   269    275     u8 isTemp;       /* True if stored in db->pBeTemp instead of db->pBe */
   270    276     u8 isTransient;  /* True if automatically deleted when VDBE finishes */
   271    277     u8 hasPrimKey;   /* True if there exists a primary key */
   272    278     u8 keyConf;      /* What to do in case of uniqueness conflict on iPKey */
          279  +
          280  +  Trigger *pTrigger; /* List of SQL triggers on this table */
   273    281   };
   274    282   
   275    283   /*
   276    284   ** SQLite supports 5 different ways to resolve a contraint
   277    285   ** error.  ROLLBACK processing means that a constraint violation
   278    286   ** causes the operation in proces to fail and for the current transaction
   279    287   ** to be rolled back.  ABORT processing means the operation in process
................................................................................
   546    554     int nSet;            /* Number of sets used so far */
   547    555     int nAgg;            /* Number of aggregate expressions */
   548    556     AggExpr *aAgg;       /* An array of aggregate expressions */
   549    557     int useAgg;          /* If true, extract field values from the aggregator
   550    558                          ** while generating expressions.  Normally false */
   551    559     int schemaVerified;  /* True if an OP_VerifySchema has been coded someplace
   552    560                          ** other than after an OP_Transaction */
          561  +
          562  +  TriggerStack * trigStack;
          563  +};
          564  +
          565  +struct TriggerStack {
          566  +  Trigger * pTrigger;
          567  +  Table *   pTab;         /* Table that triggers are currently being coded as */
          568  +  int       newIdx;       /* Index of "new" temp table */
          569  +  int       oldIdx;       /* Index of "old" temp table */
          570  +  int       orconf;       /* Current orconf policy */
          571  +  struct TriggerStack * pNext;
          572  +};
          573  +struct TriggerStep {
          574  +  int op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
          575  +  int orconf;
          576  +
          577  +  Select * pSelect;     /* Valid for SELECT and sometimes 
          578  +			   INSERT steps (when pExprList == 0) */
          579  +  Token target;         /* Valid for DELETE, UPDATE, INSERT steps */
          580  +  Expr * pWhere;        /* Valid for DELETE, UPDATE steps */
          581  +  ExprList * pExprList; /* Valid for UPDATE statements and sometimes 
          582  +			   INSERT steps (when pSelect == 0)         */
          583  +  IdList *pIdList;      /* Valid for INSERT statements only */
          584  +
          585  +  TriggerStep * pNext;  /* Next in the link-list */
          586  +};
          587  +struct Trigger {
          588  +  char * name;             /* The name of the trigger                        */
          589  +  char * table;            /* The table or view to which the trigger applies */
          590  +  int    op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT         */
          591  +  int    tr_tm;            /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD         */
          592  +  Expr * pWhen;            /* The WHEN clause of the expresion (may be NULL) */
          593  +  IdList * pColumns;       /* If this is an UPDATE OF <column-list> trigger,
          594  +			      the column names are stored in this list       */
          595  +  int foreach;             /* One of TK_ROW or TK_STATEMENT */
          596  +
          597  +  TriggerStep * step_list; /* Link list of trigger program steps             */
          598  +
          599  +  char * strings;  /* pointer to the allocation of Token strings */
          600  +  Trigger * pNext; /* Next trigger associated with the table */
          601  +  int isCommit;
   553    602   };
   554    603   
          604  +TriggerStep * sqliteTriggerSelectStep(Select *);
          605  +TriggerStep * sqliteTriggerInsertStep(Token *, IdList *, ExprList *, 
          606  +    Select *, int);
          607  +TriggerStep * sqliteTriggerUpdateStep(Token *, ExprList *, Expr *, int);
          608  +TriggerStep * sqliteTriggerDeleteStep(Token *, Expr *);
          609  +
          610  +extern int always_code_trigger_setup;
          611  +
          612  +void sqliteCreateTrigger(Parse * ,Token *, int, int, IdList *, Token *, int, Expr *, TriggerStep *, char const *,int);
          613  +void sqliteDropTrigger(Parse *, Token *, int);
          614  +int sqliteTriggersExist( Parse * , Trigger * , int , int , int, ExprList * );
          615  +int sqliteCodeRowTrigger( Parse * pParse, int op, ExprList *, int tr_tm,   Table * tbl, int newTable, int oldTable, int onError);
          616  +
          617  +void sqliteViewTriggers(Parse *, Table *, Expr *, int, ExprList *);
          618  +
   555    619   /*
   556    620   ** Internal function prototypes
   557    621   */
   558    622   int sqliteStrICmp(const char *, const char *);
   559    623   int sqliteStrNICmp(const char *, const char *, int);
   560    624   int sqliteHashNoCase(const char *, int);
   561    625   int sqliteCompare(const char *, const char *);
................................................................................
   658    722   IdList *sqliteIdListDup(IdList*);
   659    723   Select *sqliteSelectDup(Select*);
   660    724   FuncDef *sqliteFindFunction(sqlite*,const char*,int,int,int);
   661    725   void sqliteRegisterBuildinFunctions(sqlite*);
   662    726   int sqliteSafetyOn(sqlite*);
   663    727   int sqliteSafetyOff(sqlite*);
   664    728   int sqliteSafetyCheck(sqlite*);
          729  +
          730  +void changeCookie(sqlite *);

Changes to src/tokenize.c.

    11     11   *************************************************************************
    12     12   ** An tokenizer for SQL
    13     13   **
    14     14   ** This file contains C code that splits an SQL input string up into
    15     15   ** individual tokens and sends those tokens one-by-one over to the
    16     16   ** parser for analysis.
    17     17   **
    18         -** $Id: tokenize.c,v 1.40 2002/03/24 13:13:29 drh Exp $
           18  +** $Id: tokenize.c,v 1.41 2002/05/15 08:30:14 danielk1977 Exp $
    19     19   */
    20     20   #include "sqliteInt.h"
    21     21   #include "os.h"
    22     22   #include <ctype.h>
    23     23   #include <stdlib.h>
    24     24   
    25     25   /*
................................................................................
    35     35   };
    36     36   
    37     37   /*
    38     38   ** These are the keywords
    39     39   */
    40     40   static Keyword aKeywordTable[] = {
    41     41     { "ABORT",             0, TK_ABORT,            0 },
           42  +  { "AFTER",             0, TK_AFTER,            0 },
    42     43     { "ALL",               0, TK_ALL,              0 },
    43     44     { "AND",               0, TK_AND,              0 },
    44     45     { "AS",                0, TK_AS,               0 },
    45     46     { "ASC",               0, TK_ASC,              0 },
           47  +  { "BEFORE",            0, TK_BEFORE,           0 },
    46     48     { "BEGIN",             0, TK_BEGIN,            0 },
    47     49     { "BETWEEN",           0, TK_BETWEEN,          0 },
    48     50     { "BY",                0, TK_BY,               0 },
    49     51     { "CASE",              0, TK_CASE,             0 },
    50     52     { "CHECK",             0, TK_CHECK,            0 },
    51     53     { "CLUSTER",           0, TK_CLUSTER,          0 },
    52     54     { "COMMIT",            0, TK_COMMIT,           0 },
................................................................................
    57     59     { "DEFAULT",           0, TK_DEFAULT,          0 },
    58     60     { "DELETE",            0, TK_DELETE,           0 },
    59     61     { "DELIMITERS",        0, TK_DELIMITERS,       0 },
    60     62     { "DESC",              0, TK_DESC,             0 },
    61     63     { "DISTINCT",          0, TK_DISTINCT,         0 },
    62     64     { "DROP",              0, TK_DROP,             0 },
    63     65     { "END",               0, TK_END,              0 },
           66  +  { "EACH",              0, TK_EACH,             0 },
    64     67     { "ELSE",              0, TK_ELSE,             0 },
    65     68     { "EXCEPT",            0, TK_EXCEPT,           0 },
    66     69     { "EXPLAIN",           0, TK_EXPLAIN,          0 },
    67     70     { "FAIL",              0, TK_FAIL,             0 },
           71  +  { "FOR",               0, TK_FOR,              0 },
    68     72     { "FROM",              0, TK_FROM,             0 },
    69     73     { "GLOB",              0, TK_GLOB,             0 },
    70     74     { "GROUP",             0, TK_GROUP,            0 },
    71     75     { "HAVING",            0, TK_HAVING,           0 },
    72     76     { "IGNORE",            0, TK_IGNORE,           0 },
    73     77     { "IN",                0, TK_IN,               0 },
    74     78     { "INDEX",             0, TK_INDEX,            0 },
    75     79     { "INSERT",            0, TK_INSERT,           0 },
           80  +  { "INSTEAD",           0, TK_INSTEAD,          0 },
    76     81     { "INTERSECT",         0, TK_INTERSECT,        0 },
    77     82     { "INTO",              0, TK_INTO,             0 },
    78     83     { "IS",                0, TK_IS,               0 },
    79     84     { "ISNULL",            0, TK_ISNULL,           0 },
    80     85     { "KEY",               0, TK_KEY,              0 },
    81     86     { "LIKE",              0, TK_LIKE,             0 },
    82     87     { "LIMIT",             0, TK_LIMIT,            0 },
    83     88     { "NOT",               0, TK_NOT,              0 },
    84     89     { "NOTNULL",           0, TK_NOTNULL,          0 },
    85     90     { "NULL",              0, TK_NULL,             0 },
           91  +  { "OF",                0, TK_OF,               0 },
    86     92     { "OFFSET",            0, TK_OFFSET,           0 },
    87     93     { "ON",                0, TK_ON,               0 },
    88     94     { "OR",                0, TK_OR,               0 },
    89     95     { "ORDER",             0, TK_ORDER,            0 },
    90     96     { "PRAGMA",            0, TK_PRAGMA,           0 },
    91     97     { "PRIMARY",           0, TK_PRIMARY,          0 },
    92     98     { "REPLACE",           0, TK_REPLACE,          0 },
    93     99     { "ROLLBACK",          0, TK_ROLLBACK,         0 },
          100  +  { "ROW",               0, TK_ROW,              0 },
    94    101     { "SELECT",            0, TK_SELECT,           0 },
    95    102     { "SET",               0, TK_SET,              0 },
    96    103     { "TABLE",             0, TK_TABLE,            0 },
    97    104     { "TEMP",              0, TK_TEMP,             0 },
    98    105     { "TEMPORARY",         0, TK_TEMP,             0 },
    99    106     { "THEN",              0, TK_THEN,             0 },
   100    107     { "TRANSACTION",       0, TK_TRANSACTION,      0 },
          108  +  { "TRIGGER",           0, TK_TRIGGER,          0 },
   101    109     { "UNION",             0, TK_UNION,            0 },
   102    110     { "UNIQUE",            0, TK_UNIQUE,           0 },
   103    111     { "UPDATE",            0, TK_UPDATE,           0 },
   104    112     { "USING",             0, TK_USING,            0 },
   105    113     { "VACUUM",            0, TK_VACUUM,           0 },
   106    114     { "VALUES",            0, TK_VALUES,           0 },
   107    115     { "VIEW",              0, TK_VIEW,             0 },

Added src/trigger.c.

            1  +/*
            2  + * All copyright on this work is disclaimed by the author.
            3  + *
            4  + */
            5  +
            6  +#include "sqliteInt.h"
            7  +/*
            8  + * This is called by the parser when it sees a CREATE TRIGGER statement
            9  + */
           10  +void 
           11  +sqliteCreateTrigger(
           12  +    Parse * pParse, /* The parse context of the CREATE TRIGGER statement */
           13  +    Token * nm,     /* The name of the trigger */
           14  +    int tr_tm,      /* One of TK_BEFORE, TK_AFTER */
           15  +    int op,         /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
           16  +    IdList * cols,  /* column list if this is an UPDATE OF trigger */
           17  +    Token * tbl,    /* The name of the table/view the trigger applies to */
           18  +    int foreach,    /* One of TK_ROW or TK_STATEMENT */
           19  +    Expr * pWhen,   /* WHEN clause */
           20  +    TriggerStep * steps,      /* The triggered program */
           21  +    char const * cc, int len) /* The string data to make persistent */
           22  +{
           23  +  Trigger * nt;
           24  +  Table   * tab;
           25  +  int offset;
           26  +  TriggerStep * ss;
           27  +
           28  +  /* Check that: 
           29  +     1. the trigger name does not already exist.
           30  +     2. the table (or view) does exist.
           31  +   */
           32  +  {
           33  +    char * tmp_str = sqliteStrNDup(nm->z, nm->n);
           34  +    if (sqliteHashFind(&(pParse->db->trigHash), tmp_str, nm->n + 1)) {
           35  +      sqliteSetNString(&pParse->zErrMsg, "trigger ", -1,
           36  +	  nm->z, nm->n, " already exists", -1, 0);
           37  +      sqliteFree(tmp_str);
           38  +      pParse->nErr++;
           39  +      goto trigger_cleanup;
           40  +    }
           41  +    sqliteFree(tmp_str);
           42  +  }
           43  +  {
           44  +    char * tmp_str = sqliteStrNDup(tbl->z, tbl->n);
           45  +    tab = sqliteFindTable(pParse->db, tmp_str);
           46  +    sqliteFree(tmp_str);
           47  +    if (!tab) {
           48  +      sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1,
           49  +	  tbl->z, tbl->n, 0);
           50  +      pParse->nErr++;
           51  +      goto trigger_cleanup;
           52  +    }
           53  +  }
           54  +
           55  +  /* Build the Trigger object */
           56  +  nt = (Trigger *)sqliteMalloc(sizeof(Trigger));
           57  +
           58  +  nt->name = sqliteStrNDup(nm->z, nm->n);
           59  +  nt->table = sqliteStrNDup(tbl->z, tbl->n);
           60  +  nt->op = op;
           61  +  nt->tr_tm = tr_tm;
           62  +  nt->pWhen = pWhen;
           63  +  nt->pColumns = cols;
           64  +  nt->foreach = foreach;
           65  +  nt->step_list = steps;
           66  +  nt->isCommit = 0;
           67  +
           68  +  nt->strings = sqliteStrNDup(cc, len);
           69  +  offset = (int)(nt->strings - cc);
           70  +
           71  +  sqliteExprMoveStrings(nt->pWhen, offset);
           72  +
           73  +  ss = nt->step_list;
           74  +  while (ss) {
           75  +    sqliteSelectMoveStrings(ss->pSelect, offset);
           76  +    if (ss->target.z) ss->target.z += offset;
           77  +    sqliteExprMoveStrings(ss->pWhere, offset);
           78  +    sqliteExprListMoveStrings(ss->pExprList, offset);
           79  +
           80  +    ss = ss->pNext;
           81  +  }
           82  +
           83  +  /* if we are not initializing, and this trigger is not on a TEMP table, 
           84  +     build the sqlite_master entry */
           85  +  if (!pParse->initFlag && !tab->isTemp) {
           86  +
           87  +    /* Make an entry in the sqlite_master table */
           88  +    sqliteBeginWriteOperation(pParse);
           89  +
           90  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_OpenWrite, 0, 2);
           91  +    sqliteVdbeChangeP3(pParse->pVdbe, -1, MASTER_NAME,           P3_STATIC);
           92  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_NewRecno,  0, 0);
           93  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
           94  +    sqliteVdbeChangeP3(pParse->pVdbe, -1, "trigger",             P3_STATIC);
           95  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
           96  +    sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->name,        0); 
           97  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
           98  +    sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->table,        0); 
           99  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_Integer,    0, 0);
          100  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
          101  +    sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->strings,     0);
          102  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_MakeRecord, 5, 0);
          103  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_PutIntKey, 0, 1);
          104  +
          105  +    /* Change the cookie, since the schema is changed */
          106  +    changeCookie(pParse->db);
          107  +    sqliteVdbeAddOp(pParse->pVdbe, OP_Integer, pParse->db->next_cookie, 0);
          108  +    sqliteVdbeAddOp(pParse->pVdbe, OP_SetCookie, 0, 0);
          109  +
          110  +    sqliteVdbeAddOp(pParse->pVdbe,        OP_Close,     0, 0);
          111  +
          112  +    sqliteEndWriteOperation(pParse);
          113  +  }
          114  +
          115  +  if (!pParse->explain) {
          116  +    /* Stick it in the hash-table */
          117  +    sqliteHashInsert(&(pParse->db->trigHash), nt->name, nm->n + 1, nt);
          118  +
          119  +    /* Attach it to the table object */
          120  +    nt->pNext = tab->pTrigger;
          121  +    tab->pTrigger = nt;
          122  +    return;
          123  +  } else {
          124  +    sqliteFree(nt->strings);
          125  +    sqliteFree(nt->name);
          126  +    sqliteFree(nt->table);
          127  +    sqliteFree(nt);
          128  +  }
          129  +
          130  +trigger_cleanup:
          131  +
          132  +  sqliteIdListDelete(cols);
          133  +  sqliteExprDelete(pWhen);
          134  +  {
          135  +    TriggerStep * pp;
          136  +    TriggerStep * nn;
          137  +
          138  +    pp = steps;
          139  +    while (pp) {
          140  +      nn = pp->pNext;
          141  +      sqliteExprDelete(pp->pWhere);
          142  +      sqliteExprListDelete(pp->pExprList);
          143  +      sqliteSelectDelete(pp->pSelect);
          144  +      sqliteIdListDelete(pp->pIdList);
          145  +      sqliteFree(pp);
          146  +      pp = nn;
          147  +    }
          148  +  }
          149  +}
          150  +
          151  +  TriggerStep * 
          152  +sqliteTriggerSelectStep(Select * s)
          153  +{
          154  +  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));
          155  +
          156  +  tt->op = TK_SELECT;
          157  +  tt->pSelect = s;
          158  +  tt->orconf = OE_Default;
          159  +
          160  +  return tt;
          161  +}
          162  +
          163  +TriggerStep * 
          164  +sqliteTriggerInsertStep(Token * tbl, IdList * col, ExprList * val, Select * s, int orconf)
          165  +{
          166  +  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));
          167  +
          168  +  assert(val == 0 || s == 0);
          169  +  assert(val != 0 || s != 0);
          170  +
          171  +  tt->op = TK_INSERT;
          172  +  tt->pSelect = s;
          173  +  tt->target  = *tbl;
          174  +  tt->pIdList = col;
          175  +  tt->pExprList = val;
          176  +  tt->orconf = orconf;
          177  +
          178  +  return tt;
          179  +}
          180  +
          181  +TriggerStep * 
          182  +sqliteTriggerUpdateStep(Token * tbl, ExprList * val, Expr * w, int orconf)
          183  +{
          184  +  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));
          185  +
          186  +  tt->op = TK_UPDATE;
          187  +  tt->target  = *tbl;
          188  +  tt->pExprList = val;
          189  +  tt->pWhere = w;
          190  +  tt->orconf = orconf;
          191  +
          192  +  return tt;
          193  +}
          194  +
          195  +TriggerStep * 
          196  +sqliteTriggerDeleteStep(Token * tbl, Expr * w)
          197  +{
          198  +  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));
          199  +
          200  +  tt->op = TK_DELETE;
          201  +  tt->target  = *tbl;
          202  +  tt->pWhere = w;
          203  +  tt->orconf = OE_Default;
          204  +
          205  +  return tt;
          206  +}
          207  +
          208  +
          209  +/* This does a recursive delete of the trigger structure */
          210  +void sqliteDeleteTrigger(Trigger * tt)
          211  +{
          212  +  TriggerStep * ts, * tc;
          213  +  ts = tt->step_list;
          214  +
          215  +  while (ts) {
          216  +    tc = ts;
          217  +    ts = ts->pNext;
          218  +
          219  +    sqliteExprDelete(tc->pWhere);
          220  +    sqliteExprListDelete(tc->pExprList);
          221  +    sqliteSelectDelete(tc->pSelect);
          222  +    sqliteIdListDelete(tc->pIdList);
          223  +
          224  +    sqliteFree(tc);
          225  +  }
          226  +
          227  +  sqliteFree(tt->name);
          228  +  sqliteFree(tt->table);
          229  +  sqliteExprDelete(tt->pWhen);
          230  +  sqliteIdListDelete(tt->pColumns);
          231  +  sqliteFree(tt->strings);
          232  +  sqliteFree(tt);
          233  +}
          234  +
          235  +/*
          236  + * "nested" is true if this is begin called as the result of a DROP TABLE
          237  + */
          238  +void sqliteDropTrigger(Parse *pParse, Token * trigname, int nested)
          239  +{
          240  +  char * tmp_name;
          241  +  Trigger * trig;
          242  +  Table   * tbl;
          243  +
          244  +  tmp_name = sqliteStrNDup(trigname->z, trigname->n);
          245  +
          246  +  /* ensure that the trigger being dropped exists */
          247  +  trig = sqliteHashFind(&(pParse->db->trigHash), tmp_name, trigname->n + 1); 
          248  +  if (!trig) {
          249  +    sqliteSetNString(&pParse->zErrMsg, "no such trigger: ", -1,
          250  +	tmp_name, -1, 0);
          251  +    sqliteFree(tmp_name);
          252  +    return;
          253  +  }
          254  +
          255  +  /*
          256  +   * If this is not an "explain", do the following:
          257  +   * 1. Remove the trigger from its associated table structure
          258  +   * 2. Move the trigger from the trigHash hash to trigDrop
          259  +   */
          260  +  if (!pParse->explain) {
          261  +    /* 1 */
          262  +    tbl = sqliteFindTable(pParse->db, trig->table);
          263  +    assert(tbl);
          264  +    if (tbl->pTrigger == trig) 
          265  +      tbl->pTrigger = trig->pNext;
          266  +    else {
          267  +      Trigger * cc = tbl->pTrigger;
          268  +      while (cc) {
          269  +	if (cc->pNext == trig) {
          270  +	  cc->pNext = cc->pNext->pNext;
          271  +	  break;
          272  +	}
          273  +	cc = cc->pNext;
          274  +      }
          275  +      assert(cc);
          276  +    }
          277  +
          278  +    /* 2 */
          279  +    sqliteHashInsert(&(pParse->db->trigHash), tmp_name, 
          280  +	trigname->n + 1, NULL);
          281  +    sqliteHashInsert(&(pParse->db->trigDrop), trig->name, 
          282  +	trigname->n + 1, trig);
          283  +  }
          284  +
          285  +  /* Unless this is a trigger on a TEMP TABLE, generate code to destroy the
          286  +   * database record of the trigger */
          287  +  if (!tbl->isTemp) {
          288  +    int base;
          289  +    static VdbeOp dropTrigger[] = {
          290  +      { OP_OpenWrite,  0, 2,        MASTER_NAME},
          291  +      { OP_Rewind,     0, ADDR(9),  0},
          292  +      { OP_String,     0, 0,        0}, /* 2 */
          293  +      { OP_MemStore,   1, 1,        0},
          294  +      { OP_MemLoad,    1, 0,        0}, /* 4 */
          295  +      { OP_Column,     0, 1,        0},
          296  +      { OP_Ne,         0, ADDR(8),  0},
          297  +      { OP_Delete,     0, 0,        0},
          298  +      { OP_Next,       0, ADDR(4),  0}, /* 8 */
          299  +      { OP_Integer,    0, 0,        0}, /* 9 */
          300  +      { OP_SetCookie,  0, 0,        0},
          301  +      { OP_Close,      0, 0,        0},
          302  +    };
          303  +
          304  +    if (!nested) 
          305  +      sqliteBeginWriteOperation(pParse);
          306  +
          307  +    base = sqliteVdbeAddOpList(pParse->pVdbe, 
          308  +	ArraySize(dropTrigger), dropTrigger);
          309  +    sqliteVdbeChangeP3(pParse->pVdbe, base+2, tmp_name, 0);
          310  +
          311  +    if (!nested)
          312  +      changeCookie(pParse->db);
          313  +
          314  +    sqliteVdbeChangeP1(pParse->pVdbe, base+9, pParse->db->next_cookie);
          315  +
          316  +    if (!nested)
          317  +      sqliteEndWriteOperation(pParse);
          318  +  }
          319  +
          320  +  sqliteFree(tmp_name);
          321  +}
          322  +
          323  +static int checkColumnOverLap(IdList * ii, ExprList * ee)
          324  +{
          325  +  int i, e;
          326  +  if (!ii) return 1;
          327  +  if (!ee) return 1;
          328  +
          329  +  for (i = 0; i < ii->nId; i++) 
          330  +    for (e = 0; e < ee->nExpr; e++) 
          331  +      if (!sqliteStrICmp(ii->a[i].zName, ee->a[e].zName))
          332  +	return 1;
          333  +
          334  +  return 0; 
          335  +}
          336  +
          337  +/* A global variable that is TRUE if we should always set up temp tables for
          338  + * for triggers, even if there are no triggers to code. This is used to test 
          339  + * how much overhead the triggers algorithm is causing.
          340  + *
          341  + * This flag can be set or cleared using the "trigger_overhead_test" pragma.
          342  + * The pragma is not documented since it is not really part of the interface
          343  + * to SQLite, just the test procedure.
          344  +*/
          345  +int always_code_trigger_setup = 0;
          346  +
          347  +/*
          348  + * Returns true if a trigger matching op, tr_tm and foreach that is NOT already
          349  + * on the Parse objects trigger-stack (to prevent recursive trigger firing) is
          350  + * found in the list specified as pTrigger.
          351  + */
          352  +int sqliteTriggersExist(
          353  +    Parse * pParse, 
          354  +    Trigger * pTrigger,
          355  +    int op,                 /* one of TK_DELETE, TK_INSERT, TK_UPDATE */
          356  +    int tr_tm,              /* one of TK_BEFORE, TK_AFTER */
          357  +    int foreach,            /* one of TK_ROW or TK_STATEMENT */
          358  +    ExprList * pChanges)
          359  +{
          360  +  Trigger * tt;
          361  +
          362  +  if (always_code_trigger_setup) return 1;
          363  +
          364  +  tt = pTrigger;
          365  +  while (tt) {
          366  +    if (tt->op == op && tt->tr_tm == tr_tm && tt->foreach == foreach &&
          367  +	checkColumnOverLap(tt->pColumns, pChanges)) {
          368  +      TriggerStack * ss;
          369  +      ss = pParse->trigStack;
          370  +      while (ss && ss->pTrigger != pTrigger) ss = ss->pNext;
          371  +      if (!ss) return 1;
          372  +    }
          373  +    tt = tt->pNext;
          374  +  }
          375  +
          376  +  return 0;
          377  +}
          378  +
          379  +static int codeTriggerProgram(
          380  +	Parse *pParse,
          381  +	TriggerStep * program,
          382  +	int onError)
          383  +{
          384  +    TriggerStep * step = program;
          385  +    int orconf;
          386  +
          387  +    while (step) {
          388  +	int saveNTab = pParse->nTab;
          389  +	orconf = (onError == OE_Default)?step->orconf:onError;
          390  +	pParse->trigStack->orconf = orconf;
          391  +	switch(step->op) {
          392  +	    case TK_SELECT: {
          393  +                int tmp_tbl = pParse->nTab++;
          394  +		sqliteVdbeAddOp(pParse->pVdbe, OP_OpenTemp, tmp_tbl, 0);
          395  +		sqliteVdbeAddOp(pParse->pVdbe, OP_KeyAsData, tmp_tbl, 1);
          396  +		sqliteSelect(pParse, step->pSelect, 
          397  +			SRT_Union, tmp_tbl, 0, 0, 0);
          398  +		sqliteVdbeAddOp(pParse->pVdbe, OP_Close, tmp_tbl, 0);
          399  +		pParse->nTab--;
          400  +		break;
          401  +			    }
          402  +	    case TK_UPDATE: {
          403  +                sqliteVdbeAddOp(pParse->pVdbe, OP_PushList, 0, 0);
          404  +		sqliteUpdate(pParse, &step->target, 
          405  +			sqliteExprListDup(step->pExprList), 
          406  +			sqliteExprDup(step->pWhere), orconf);
          407  +                sqliteVdbeAddOp(pParse->pVdbe, OP_PopList, 0, 0);
          408  +		break;
          409  +			    }
          410  +	    case TK_INSERT: {
          411  +                sqliteInsert(pParse, &step->target, 
          412  +			sqliteExprListDup(step->pExprList), 
          413  +			sqliteSelectDup(step->pSelect), 
          414  +			sqliteIdListDup(step->pIdList), orconf);
          415  +		break;
          416  +			    }
          417  +	    case TK_DELETE: {
          418  +		sqliteVdbeAddOp(pParse->pVdbe, OP_PushList, 0, 0);
          419  +                sqliteDeleteFrom(pParse, &step->target, 
          420  +			sqliteExprDup(step->pWhere)
          421  +			);
          422  +		sqliteVdbeAddOp(pParse->pVdbe, OP_PopList, 0, 0);
          423  +		break;
          424  +			    }
          425  +	    default:
          426  +			    assert(0);
          427  +	} 
          428  +	pParse->nTab = saveNTab;
          429  +	step = step->pNext;
          430  +    }
          431  +
          432  +    return 0;
          433  +}
          434  +
          435  +int sqliteCodeRowTrigger(
          436  +	Parse * pParse,  /* Parse context */
          437  +	int op,          /* One of TK_UPDATE, TK_INSERT, TK_DELETE */
          438  +	ExprList * changes, /* Changes list for any UPDATE OF triggers */
          439  +	int tr_tm,       /* One of TK_BEFORE, TK_AFTER */
          440  +	Table * tbl,     /* The table to code triggers from */
          441  +	int newTable,    /* The indice of the "new" row to access */
          442  +	int oldTable,    /* The indice of the "old" row to access */
          443  +	int onError)     /* ON CONFLICT policy */
          444  +{
          445  +  Trigger * pTrigger;
          446  +  TriggerStack * pTriggerStack;
          447  +
          448  +
          449  +  assert(op == TK_UPDATE || op == TK_INSERT || op == TK_DELETE);
          450  +  assert(tr_tm == TK_BEFORE || tr_tm == TK_AFTER);
          451  +
          452  +  assert(newTable != -1 || oldTable != -1);
          453  +
          454  +  pTrigger = tbl->pTrigger;
          455  +  while (pTrigger) {
          456  +    int fire_this = 0;
          457  +
          458  +    /* determine whether we should code this trigger */
          459  +    if (pTrigger->op == op && pTrigger->tr_tm == tr_tm && 
          460  +	pTrigger->foreach == TK_ROW) {
          461  +      fire_this = 1;
          462  +      pTriggerStack = pParse->trigStack;
          463  +      while (pTriggerStack) {
          464  +	if (pTriggerStack->pTrigger == pTrigger) fire_this = 0;
          465  +	pTriggerStack = pTriggerStack->pNext;
          466  +      }
          467  +      if (op == TK_UPDATE && pTrigger->pColumns &&
          468  +	  !checkColumnOverLap(pTrigger->pColumns, changes))
          469  +	fire_this = 0;
          470  +    }
          471  +
          472  +    if (fire_this) {
          473  +      int endTrigger;
          474  +      IdList dummyTablist;
          475  +      Expr * whenExpr;
          476  +
          477  +      dummyTablist.nId = 0;
          478  +      dummyTablist.a = 0;
          479  +
          480  +      /* Push an entry on to the trigger stack */
          481  +      pTriggerStack = sqliteMalloc(sizeof(TriggerStack));
          482  +      pTriggerStack->pTrigger = pTrigger;
          483  +      pTriggerStack->newIdx = newTable;
          484  +      pTriggerStack->oldIdx = oldTable;
          485  +      pTriggerStack->pTab = tbl;
          486  +      pTriggerStack->pNext = pParse->trigStack;
          487  +      pParse->trigStack = pTriggerStack;
          488  +
          489  +      /* code the WHEN clause */
          490  +      endTrigger = sqliteVdbeMakeLabel(pParse->pVdbe);
          491  +      whenExpr = sqliteExprDup(pTrigger->pWhen);
          492  +      if (sqliteExprResolveIds(pParse, 0, &dummyTablist, 0, whenExpr)) {
          493  +	pParse->trigStack = pParse->trigStack->pNext;
          494  +	sqliteFree(pTriggerStack);
          495  +	sqliteExprDelete(whenExpr);
          496  +	return 1;
          497  +      }
          498  +      sqliteExprIfFalse(pParse, whenExpr, endTrigger);
          499  +      sqliteExprDelete(whenExpr);
          500  +
          501  +      codeTriggerProgram(pParse, pTrigger->step_list, onError); 
          502  +
          503  +      /* Pop the entry off the trigger stack */
          504  +      pParse->trigStack = pParse->trigStack->pNext;
          505  +      sqliteFree(pTriggerStack);
          506  +
          507  +      sqliteVdbeResolveLabel(pParse->pVdbe, endTrigger);
          508  +    }
          509  +    pTrigger = pTrigger->pNext;
          510  +  }
          511  +
          512  +  return 0;
          513  +}
          514  +
          515  +/*
          516  + * Handle UPDATE and DELETE triggers on views
          517  + */
          518  +void sqliteViewTriggers(Parse *pParse, Table *pTab, 
          519  +    Expr * pWhere, int onError, ExprList * pChanges)
          520  +{
          521  +  int oldIdx = -1;
          522  +  int newIdx = -1;
          523  +  int *aXRef = 0;   
          524  +  Vdbe *v;
          525  +  int endOfLoop;
          526  +  int startOfLoop;
          527  +  Select theSelect;
          528  +  Token tblNameToken;
          529  +
          530  +  assert(pTab->pSelect);
          531  +
          532  +  tblNameToken.z = pTab->zName;
          533  +  tblNameToken.n = strlen(pTab->zName);
          534  +
          535  +  theSelect.isDistinct = 0;
          536  +  theSelect.pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL, 0, 0, 0), 0);
          537  +  theSelect.pSrc   = sqliteIdListAppend(0, &tblNameToken);
          538  +  theSelect.pWhere = pWhere;    pWhere = 0;
          539  +  theSelect.pGroupBy = 0;
          540  +  theSelect.pHaving = 0;
          541  +  theSelect.pOrderBy = 0;
          542  +  theSelect.op = TK_SELECT; /* ?? */
          543  +  theSelect.pPrior = 0;
          544  +  theSelect.nLimit = -1;
          545  +  theSelect.nOffset = -1;
          546  +  theSelect.zSelect = 0;
          547  +  theSelect.base = 0;
          548  +
          549  +  v = sqliteGetVdbe(pParse);
          550  +  assert(v);
          551  +  sqliteBeginMultiWriteOperation(pParse);
          552  +
          553  +  /* Allocate temp tables */
          554  +  oldIdx = pParse->nTab++;
          555  +  sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0);
          556  +  if (pChanges) {
          557  +    newIdx = pParse->nTab++;
          558  +    sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
          559  +  }
          560  +
          561  +  /* Snapshot the view */
          562  +  if (sqliteSelect(pParse, &theSelect, SRT_Table, oldIdx, 0, 0, 0)) {
          563  +    goto trigger_cleanup;
          564  +  }
          565  +
          566  +  /* loop thru the view snapshot, executing triggers for each row */
          567  +  endOfLoop = sqliteVdbeMakeLabel(v);
          568  +  sqliteVdbeAddOp(v, OP_Rewind, oldIdx, endOfLoop);
          569  +
          570  +  /* Loop thru the view snapshot, executing triggers for each row */
          571  +  startOfLoop = sqliteVdbeCurrentAddr(v);
          572  +
          573  +  /* Build the updated row if required */
          574  +  if (pChanges) {
          575  +    int ii, jj;
          576  +
          577  +    aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
          578  +    if( aXRef==0 ) goto trigger_cleanup;
          579  +    for (ii = 0; ii < pTab->nCol; ii++)
          580  +      aXRef[ii] = -1;
          581  +
          582  +    for(ii=0; ii<pChanges->nExpr; ii++){
          583  +      int jj;
          584  +      if( sqliteExprResolveIds(pParse, oldIdx, theSelect.pSrc , 0, 
          585  +	    pChanges->a[ii].pExpr) )
          586  +	goto trigger_cleanup;
          587  +
          588  +      if( sqliteExprCheck(pParse, pChanges->a[ii].pExpr, 0, 0) )
          589  +	goto trigger_cleanup;
          590  +
          591  +      for(jj=0; jj<pTab->nCol; jj++){
          592  +	if( sqliteStrICmp(pTab->aCol[jj].zName, pChanges->a[ii].zName)==0 ){
          593  +	  aXRef[jj] = ii;
          594  +	  break;
          595  +	}
          596  +      }
          597  +      if( jj>=pTab->nCol ){
          598  +	sqliteSetString(&pParse->zErrMsg, "no such column: ", 
          599  +	    pChanges->a[ii].zName, 0);
          600  +	pParse->nErr++;
          601  +	goto trigger_cleanup;
          602  +      }
          603  +    }
          604  +
          605  +    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
          606  +
          607  +    for (ii = 0; ii < pTab->nCol; ii++)
          608  +      if( aXRef[ii] < 0 ) 
          609  +	sqliteVdbeAddOp(v, OP_Column, oldIdx, ii);
          610  +      else
          611  +	sqliteExprCode(pParse, pChanges->a[aXRef[ii]].pExpr);
          612  +
          613  +    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
          614  +    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
          615  +    sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);
          616  +
          617  +    sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, 
          618  +	pTab, newIdx, oldIdx, onError);
          619  +    sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, 
          620  +	pTab, newIdx, oldIdx, onError);
          621  +  } else {
          622  +    sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, oldIdx, 
          623  +	onError);
          624  +    sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, oldIdx, 
          625  +	onError);
          626  +  }
          627  +
          628  +  sqliteVdbeAddOp(v, OP_Next, oldIdx, startOfLoop);
          629  +
          630  +  sqliteVdbeResolveLabel(v, endOfLoop);
          631  +  sqliteEndWriteOperation(pParse);
          632  +
          633  +trigger_cleanup:
          634  +  sqliteFree(aXRef);
          635  +  sqliteExprListDelete(pChanges);
          636  +  sqliteExprDelete(pWhere);
          637  +  sqliteExprListDelete(theSelect.pEList);
          638  +  sqliteIdListDelete(theSelect.pSrc);
          639  +  sqliteExprDelete(theSelect.pWhere);
          640  +  return;
          641  +}
          642  +
          643  +

Changes to src/update.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle UPDATE statements.
    14     14   **
    15         -** $Id: update.c,v 1.36 2002/03/03 18:59:41 drh Exp $
           15  +** $Id: update.c,v 1.37 2002/05/15 08:30:14 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Process an UPDATE statement.
    21     21   */
    22     22   void sqliteUpdate(
................................................................................
    43     43                            ** an expression for the i-th column of the table.
    44     44                            ** aXRef[i]==-1 if the i-th column is not changed. */
    45     45     int openOp;            /* Opcode used to open tables */
    46     46     int chngRecno;         /* True if the record number is being changed */
    47     47     Expr *pRecnoExpr;      /* Expression defining the new record number */
    48     48     int openAll;           /* True if all indices need to be opened */
    49     49   
           50  +  int row_triggers_exist = 0;
           51  +
           52  +  int newIdx      = -1;  /* index of trigger "new" temp table       */
           53  +  int oldIdx      = -1;  /* index of trigger "old" temp table       */
           54  +
    50     55     if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
    51     56     db = pParse->db;
           57  +
           58  +  /* Check for the special case of a VIEW with one or more ON UPDATE triggers 
           59  +   * defined 
           60  +   */
           61  +  {
           62  +    char * zTab = sqliteTableNameFromToken(pTableName);
           63  +
           64  +    if(zTab != 0) {
           65  +      pTab = sqliteFindTable(pParse->db, zTab);
           66  +      if (pTab) {
           67  +	row_triggers_exist = 
           68  +	  sqliteTriggersExist(pParse, pTab->pTrigger, 
           69  +	      TK_UPDATE, TK_BEFORE, TK_ROW, pChanges) ||
           70  +	  sqliteTriggersExist(pParse, pTab->pTrigger, 
           71  +	      TK_UPDATE, TK_AFTER, TK_ROW, pChanges);
           72  +      }
           73  +      sqliteFree(zTab);
           74  +      if (row_triggers_exist &&  pTab->pSelect ) {
           75  +	/* Just fire VIEW triggers */
           76  +	sqliteViewTriggers(pParse, pTab, pWhere, onError, pChanges);
           77  +	return;
           78  +      }
           79  +    }
           80  +  }
    52     81   
    53     82     /* Locate the table which we want to update.  This table has to be
    54     83     ** put in an IdList structure because some of the subroutines we
    55     84     ** will be calling are designed to work with multiple tables and expect
    56     85     ** an IdList* parameter instead of just a Table* parameter.
    57     86     */
    58     87     pTabList = sqliteTableTokenToIdList(pParse, pTableName);
    59     88     if( pTabList==0 ) goto update_cleanup;
    60     89     pTab = pTabList->a[0].pTab;
    61     90     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
    62     91     aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
    63     92     if( aXRef==0 ) goto update_cleanup;
    64     93     for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
           94  +
           95  +  if (row_triggers_exist) {
           96  +    newIdx = pParse->nTab++;
           97  +    oldIdx = pParse->nTab++;
           98  +  }
    65     99   
    66    100     /* Resolve the column names in all the expressions in both the
    67    101     ** WHERE clause and in the new values.  Also find the column index
    68    102     ** for each column to be updated in the pChanges array.
    69    103     */
    70    104     base = pParse->nTab++;
    71    105     if( pWhere ){
................................................................................
   155    189   
   156    190     /* End the database scan loop.
   157    191     */
   158    192     sqliteWhereEnd(pWInfo);
   159    193   
   160    194     /* Initialize the count of updated rows
   161    195     */
   162         -  if( db->flags & SQLITE_CountRows ){
          196  +  if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
   163    197       sqliteVdbeAddOp(v, OP_Integer, 0, 0);
   164    198     }
          199  +
          200  +  if (row_triggers_exist) {
          201  +    int ii;
          202  +
          203  +    sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0);
          204  +    sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
          205  +
          206  +    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
          207  +    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
          208  +    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
          209  +
          210  +    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
          211  +    sqliteVdbeAddOp(v, OP_Open, base, pTab->tnum);
          212  +    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
          213  +
          214  +    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
          215  +    for (ii = 0; ii < pTab->nCol; ii++) {
          216  +	if (ii == pTab->iPKey) 
          217  +	    sqliteVdbeAddOp(v, OP_Recno, base, 0);
          218  +	else
          219  +	    sqliteVdbeAddOp(v, OP_Column, base, ii);
          220  +    }
          221  +    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
          222  +    sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
          223  +
          224  +    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
          225  +    for (ii = 0; ii < pTab->nCol; ii++){
          226  +      if( aXRef[ii] < 0 ){
          227  +        if (ii == pTab->iPKey)
          228  +          sqliteVdbeAddOp(v, OP_Recno, base, 0);
          229  +        else
          230  +          sqliteVdbeAddOp(v, OP_Column, base, ii);
          231  +      }else{
          232  +        sqliteExprCode(pParse, pChanges->a[aXRef[ii]].pExpr);
          233  +      }
          234  +    }
          235  +    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
          236  +    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
          237  +    sqliteVdbeAddOp(v, OP_Close, base, 0);
          238  +
          239  +    sqliteVdbeAddOp(v, OP_Rewind, oldIdx, 0);
          240  +    sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);
          241  +
          242  +    if (sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab, 
          243  +	  newIdx, oldIdx, onError)) goto update_cleanup;
          244  +  }
   165    245   
   166    246     /* Rewind the list of records that need to be updated and
   167    247     ** open every index that needs updating.  Note that if any
   168    248     ** index could potentially invoke a REPLACE conflict resolution 
   169    249     ** action, then we need to open all indices because we might need
   170    250     ** to be deleting some records.
   171    251     */
   172         -  sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
   173    252     openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
   174    253     sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
   175    254     if( onError==OE_Replace ){
   176    255       openAll = 1;
   177    256     }else{
   178    257       openAll = 0;
   179    258       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
................................................................................
   193    272   
   194    273     /* Loop over every record that needs updating.  We have to load
   195    274     ** the old data for each record to be updated because some columns
   196    275     ** might not change and we will need to copy the old value.
   197    276     ** Also, the old data is needed to delete the old index entires.
   198    277     ** So make the cursor point at the old record.
   199    278     */
   200         -  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
   201         -  sqliteVdbeAddOp(v, OP_Dup, 0, 0);
          279  +  if (!row_triggers_exist) {
          280  +    int ii;
          281  +    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
          282  +    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
          283  +    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
          284  +  }
   202    285     sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
   203    286   
   204    287     /* If the record number will change, push the record number as it
   205    288     ** will be after the update. (The old record number is currently
   206    289     ** on top of the stack.)
   207    290     */
   208    291     if( chngRecno ){
................................................................................
   237    320     /* Delete the old indices for the current record.
   238    321     */
   239    322     sqliteGenerateRowIndexDelete(v, pTab, base, aIdxUsed);
   240    323   
   241    324     /* If changing the record number, delete the old record.
   242    325     */
   243    326     if( chngRecno ){
   244         -    sqliteVdbeAddOp(v, OP_Delete, 0, 0);
          327  +    sqliteVdbeAddOp(v, OP_Delete, base, 0);
   245    328     }
   246    329   
   247    330     /* Create the new index entries and the new record.
   248    331     */
   249    332     sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno, 1);
   250    333   
   251    334     /* Increment the row counter 
   252    335     */
   253         -  if( db->flags & SQLITE_CountRows ){
          336  +  if( db->flags & SQLITE_CountRows && !pParse->trigStack){
   254    337       sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
   255    338     }
          339  +
          340  +  if (row_triggers_exist) {
          341  +    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
          342  +      if( openAll || aIdxUsed[i] )
          343  +	sqliteVdbeAddOp(v, OP_Close, base+i+1, 0);
          344  +    }
          345  +    sqliteVdbeAddOp(v, OP_Close, base, 0);
          346  +    pParse->nTab = base;
          347  +
          348  +    if (sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab, 
          349  +	  newIdx, oldIdx, onError)) goto update_cleanup;
          350  +  }
   256    351   
   257    352     /* Repeat the above with the next record to be updated, until
   258    353     ** all record selected by the WHERE clause have been updated.
   259    354     */
   260    355     sqliteVdbeAddOp(v, OP_Goto, 0, addr);
   261    356     sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
   262    357     sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
          358  +
          359  +  /* Close all tables if there were no FOR EACH ROW triggers */
          360  +  if (!row_triggers_exist) {
          361  +    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
          362  +      if( openAll || aIdxUsed[i] ){
          363  +	sqliteVdbeAddOp(v, OP_Close, base+i+1, 0);
          364  +      }
          365  +    }
          366  +    sqliteVdbeAddOp(v, OP_Close, base, 0);
          367  +    pParse->nTab = base;
          368  +  } else {
          369  +    sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
          370  +    sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
          371  +  }
          372  +
   263    373     sqliteEndWriteOperation(pParse);
   264    374   
   265    375     /*
   266    376     ** Return the number of rows that were changed.
   267    377     */
   268         -  if( db->flags & SQLITE_CountRows ){
          378  +  if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
   269    379       sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
   270    380       sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
   271    381       sqliteVdbeChangeP3(v, -1, "rows updated", P3_STATIC);
   272    382       sqliteVdbeAddOp(v, OP_Callback, 1, 0);
   273    383     }
   274    384   
   275    385   update_cleanup:

Changes to src/vdbe.c.

    26     26   ** type to the other occurs as necessary.
    27     27   ** 
    28     28   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    29     29   ** function which does the work of interpreting a VDBE program.
    30     30   ** But other routines are also provided to help in building up
    31     31   ** a program instruction by instruction.
    32     32   **
    33         -** $Id: vdbe.c,v 1.141 2002/05/10 13:14:07 drh Exp $
           33  +** $Id: vdbe.c,v 1.142 2002/05/15 08:30:14 danielk1977 Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <ctype.h>
    37     37   
    38     38   /*
    39     39   ** The following global variable is incremented every time a cursor
    40     40   ** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
   245    245     Mem *aMem;          /* The memory locations */
   246    246     Agg agg;            /* Aggregate information */
   247    247     int nSet;           /* Number of sets allocated */
   248    248     Set *aSet;          /* An array of sets */
   249    249     int nCallback;      /* Number of callbacks invoked so far */
   250    250     int iLimit;         /* Limit on the number of callbacks remaining */
   251    251     int iOffset;        /* Offset before beginning to do callbacks */
          252  +
          253  +  int keylistStackDepth; 
          254  +  Keylist ** keylistStack;
   252    255   };
   253    256   
   254    257   /*
   255    258   ** Create a new virtual database engine.
   256    259   */
   257    260   Vdbe *sqliteVdbeCreate(sqlite *db){
   258    261     Vdbe *p;
................................................................................
   995    998     AggReset(&p->agg);
   996    999     for(i=0; i<p->nSet; i++){
   997   1000       sqliteHashClear(&p->aSet[i].hash);
   998   1001     }
   999   1002     sqliteFree(p->aSet);
  1000   1003     p->aSet = 0;
  1001   1004     p->nSet = 0;
         1005  +  if (p->keylistStackDepth > 0) {
         1006  +    int ii;
         1007  +    for (ii = 0; ii < p->keylistStackDepth; ii++) {
         1008  +      KeylistFree(p->keylistStack[ii]);
         1009  +    }
         1010  +    sqliteFree(p->keylistStack);
         1011  +    p->keylistStackDepth = 0;
         1012  +    p->keylistStack = 0;
         1013  +  }
  1002   1014   }
  1003   1015   
  1004   1016   /*
  1005   1017   ** Delete an entire VDBE.
  1006   1018   */
  1007   1019   void sqliteVdbeDelete(Vdbe *p){
  1008   1020     int i;
................................................................................
  1058   1070     "MustBeInt",         "Add",               "AddImm",            "Subtract",
  1059   1071     "Multiply",          "Divide",            "Remainder",         "BitAnd",
  1060   1072     "BitOr",             "BitNot",            "ShiftLeft",         "ShiftRight",
  1061   1073     "AbsValue",          "Eq",                "Ne",                "Lt",
  1062   1074     "Le",                "Gt",                "Ge",                "IsNull",
  1063   1075     "NotNull",           "Negative",          "And",               "Or",
  1064   1076     "Not",               "Concat",            "Noop",              "Function",
  1065         -  "Limit",           
         1077  +  "Limit",             "PushList",          "PopList",           
  1066   1078   };
  1067   1079   
  1068   1080   /*
  1069   1081   ** Given the name of an opcode, return its number.  Return 0 if
  1070   1082   ** there is no match.
  1071   1083   **
  1072   1084   ** This routine is used for testing and debugging.
................................................................................
  4534   4546     if( VERIFY( i>=0 && i<p->nSet &&) 
  4535   4547          sqliteHashFind(&p->aSet[i].hash, zStack[tos], aStack[tos].n)){
  4536   4548       pc = pOp->p2 - 1;
  4537   4549     }
  4538   4550     POPSTACK;
  4539   4551     break;
  4540   4552   }
         4553  +
         4554  +/* Opcode: PushList * * * 
         4555  +**
         4556  +** Save the current Vdbe list such that it can be restored by a PopList 
         4557  +** opcode. The list is empty after this is executed.
         4558  +*/
         4559  +case OP_PushList: {
         4560  +  p->keylistStackDepth++;
         4561  +  assert(p->keylistStackDepth > 0);
         4562  +  p->keylistStack = sqliteRealloc(p->keylistStack, 
         4563  +	  sizeof(Keylist *) * p->keylistStackDepth);
         4564  +  p->keylistStack[p->keylistStackDepth - 1] = p->pList;
         4565  +  p->pList = 0;
         4566  +  break;
         4567  +}
         4568  +
         4569  +/* Opcode: PopList * * * 
         4570  +**
         4571  +** Restore the Vdbe list to the state it was in when PushList was last
         4572  +** executed.
         4573  +*/
         4574  +case OP_PopList: {
         4575  +  assert(p->keylistStackDepth > 0);
         4576  +  p->keylistStackDepth--;
         4577  +  KeylistFree(p->pList);
         4578  +  p->pList = p->keylistStack[p->keylistStackDepth];
         4579  +  p->keylistStack[p->keylistStackDepth] = 0;
         4580  +  if (p->keylistStackDepth == 0) {
         4581  +    sqliteFree(p->keylistStack);
         4582  +    p->keylistStack = 0;
         4583  +  }
         4584  +  break;
         4585  +}
  4541   4586   
  4542   4587   /* Opcode: SetNotFound P1 P2 *
  4543   4588   **
  4544   4589   ** Pop the stack once and compare the value popped off with the
  4545   4590   ** contents of set P1.  If the element popped does not exists in 
  4546   4591   ** set P1, then jump to P2.  Otherwise fall through.
  4547   4592   */

Changes to src/vdbe.h.

    11     11   *************************************************************************
    12     12   ** Header file for the Virtual DataBase Engine (VDBE)
    13     13   **
    14     14   ** This header defines the interface to the virtual database engine
    15     15   ** or VDBE.  The VDBE implements an abstract machine that runs a
    16     16   ** simple program to access and modify the underlying database.
    17     17   **
    18         -** $Id: vdbe.h,v 1.50 2002/04/20 14:24:43 drh Exp $
           18  +** $Id: vdbe.h,v 1.51 2002/05/15 08:30:14 danielk1977 Exp $
    19     19   */
    20     20   #ifndef _SQLITE_VDBE_H_
    21     21   #define _SQLITE_VDBE_H_
    22     22   #include <stdio.h>
    23     23   
    24     24   /*
    25     25   ** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
   194    194   #define OP_Not               109
   195    195   #define OP_Concat            110
   196    196   #define OP_Noop              111
   197    197   #define OP_Function          112
   198    198   
   199    199   #define OP_Limit             113
   200    200   
   201         -#define OP_MAX               113
          201  +#define OP_PushList          114
          202  +#define OP_PopList           115
          203  +
          204  +#define OP_MAX               115
   202    205   
   203    206   /*
   204    207   ** Prototypes for the VDBE interface.  See comments on the implementation
   205    208   ** for a description of what each of these routines does.
   206    209   */
   207    210   Vdbe *sqliteVdbeCreate(sqlite*);
   208    211   void sqliteVdbeCreateCallback(Vdbe*, int*);

Changes to src/where.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  Also found here are subroutines
    14     14   ** to generate VDBE code to evaluate expressions.
    15     15   **
    16         -** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $
           16  +** $Id: where.c,v 1.42 2002/05/15 08:30:14 danielk1977 Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   
    20     20   /*
    21     21   ** The query generator uses an array of instances of this structure to
    22     22   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
    23     23   ** clause subexpression is separated from the others by an AND operator.
................................................................................
   212    212     memset(aExpr, 0, sizeof(aExpr));
   213    213     nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);
   214    214   
   215    215     /* Analyze all of the subexpressions.
   216    216     */
   217    217     for(i=0; i<nExpr; i++){
   218    218       exprAnalyze(base, &aExpr[i]);
          219  +    if (pParse->trigStack && pParse->trigStack->newIdx >= 0) {
          220  +	aExpr[i].prereqRight = 
          221  +	    aExpr[i].prereqRight & ~(1 << pParse->trigStack->newIdx - base);
          222  +	aExpr[i].prereqLeft = 
          223  +	    aExpr[i].prereqLeft & ~(1 << pParse->trigStack->newIdx - base);
          224  +	aExpr[i].prereqAll = 
          225  +	    aExpr[i].prereqAll & ~(1 << pParse->trigStack->newIdx - base);
          226  +    }
          227  +    if (pParse->trigStack && pParse->trigStack->oldIdx >= 0) {
          228  +	aExpr[i].prereqRight = 
          229  +	    aExpr[i].prereqRight & ~(1 << pParse->trigStack->oldIdx - base);
          230  +	aExpr[i].prereqLeft = 
          231  +	    aExpr[i].prereqLeft & ~(1 << pParse->trigStack->oldIdx - base);
          232  +	aExpr[i].prereqAll = 
          233  +	    aExpr[i].prereqAll & ~(1 << pParse->trigStack->oldIdx - base);
          234  +    }
   219    235     }
   220    236   
   221    237     /* Figure out a good nesting order for the tables.  aOrder[0] will
   222    238     ** be the index in pTabList of the outermost table.  aOrder[1] will
   223    239     ** be the first nested loop and so on.  aOrder[pTabList->nId-1] will
   224    240     ** be the innermost loop.
   225    241     **

Added test/trigger1.test.

            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  +#
           23  +
           24  +set testdir [file dirname $argv0]
           25  +source $testdir/tester.tcl
           26  +
           27  +do_test trig_cd-1.1 {
           28  +   catchsql {
           29  +     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
           30  +       SELECT * from sqlite_master;
           31  +     END;
           32  +   } 
           33  +} {1 {no such table: no_such_table}}
           34  +
           35  +execsql {
           36  +    CREATE TABLE t1(a);
           37  +}
           38  +execsql {
           39  +	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
           40  +	  INSERT INTO t1 values(1);
           41  + 	END;
           42  +}
           43  +do_test trig_cd-1.2 {
           44  +    catchsql {
           45  +	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
           46  +	    SELECT * FROM sqlite_master;
           47  + 	END
           48  +     }
           49  +} {1 {trigger tr1 already exists}}
           50  +
           51  +do_test trig_cd-1.3 {
           52  +    catchsql {
           53  +	BEGIN;
           54  +	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
           55  +	    SELECT * from sqlite_master; END;
           56  +        ROLLBACK;
           57  +	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
           58  +	    SELECT * from sqlite_master; END;
           59  +    }
           60  +} {0 {}}
           61  +
           62  +do_test trig_cd-1.4 {
           63  +    catchsql {
           64  +	DROP TRIGGER tr1;
           65  +	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
           66  +	    SELECT * FROM sqlite_master;
           67  +	END
           68  +    }
           69  +} {0 {}}
           70  +
           71  +do_test trig_cd-1.5 {
           72  +    execsql {
           73  +	BEGIN;
           74  +	DROP TRIGGER tr2;
           75  +	ROLLBACK;
           76  +	DROP TRIGGER tr2;
           77  +    }
           78  +} {}
           79  +
           80  +do_test trig_cd-1.6 {
           81  +    catchsql {
           82  +	DROP TRIGGER biggles;
           83  +    }
           84  +} {1 {no such trigger: biggles}}
           85  +
           86  +do_test trig_cd-1.7 {
           87  +    catchsql {
           88  +	DROP TABLE t1;
           89  +	DROP TRIGGER tr1;
           90  +    }
           91  +} {1 {no such trigger: tr1}}
           92  +
           93  +execsql {
           94  +  CREATE TEMP TABLE temp_table(a);
           95  +}
           96  +do_test trig_cd-1.8 {
           97  +  execsql {
           98  +	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
           99  +	    SELECT * from sqlite_master;
          100  +	END;
          101  +	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
          102  +  } 
          103  +} {0}
          104  +
          105  +catchsql {
          106  +  DROP TABLE temp_table;
          107  +}
          108  +catchsql {
          109  +  DROP TABLE t1;
          110  +}
          111  +
          112  +finish_test
          113  +

Added test/trigger2.test.

            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  +# Regression testing of FOR EACH ROW table triggers
           11  +#
           12  +# 1. Trigger execution order tests. 
           13  +# These tests ensure that BEFORE and AFTER triggers are fired at the correct
           14  +# times relative to each other and the triggering statement. 
           15  +#
           16  +# trig-1.1.*: ON UPDATE trigger execution model.
           17  +# trig-1.2.*: DELETE trigger execution model.
           18  +# trig-1.3.*: INSERT trigger execution model.
           19  +#
           20  +# 2. Trigger program execution tests.
           21  +# These tests ensure that trigger programs execute correctly (ie. that a
           22  +# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
           23  +# statements, and combinations thereof).
           24  +#
           25  +# 3. Selective trigger execution 
           26  +# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
           27  +# with WHEN clauses) are fired only fired when they are supposed to be.
           28  +#
           29  +# trig-3.1: UPDATE OF triggers
           30  +# trig-3.2: WHEN clause
           31  +#
           32  +# 4. Cascaded trigger execution 
           33  +# Tests that trigger-programs may cause other triggers to fire. Also that a 
           34  +# trigger-program is never executed recursively.
           35  +# 
           36  +# trig-4.1: Trivial cascading trigger
           37  +# trig-4.2: Trivial recursive trigger handling 
           38  +#
           39  +# 5. Count changes behaviour.
           40  +# Verify that rows altered by triggers are not included in the return value
           41  +# of the "count changes" interface.
           42  +#
           43  +# 6. ON CONFLICT clause handling
           44  +# trig-6.1[a-f]: INSERT statements
           45  +# trig-6.2[a-f]: UPDATE statements
           46  +#
           47  +# 7. Triggers on views fire correctly.
           48  +#
           49  +
           50  +set testdir [file dirname $argv0]
           51  +source $testdir/tester.tcl
           52  +
           53  +# 1.
           54  +set ii 0
           55  +foreach tbl_defn [ list \
           56  +	{CREATE TABLE tbl (a, b);} \
           57  +	{CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \
           58  +        {CREATE TABLE tbl (a, b PRIMARY KEY);} \
           59  +	{CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} ] {
           60  +  incr ii
           61  +  catchsql { DROP INDEX tbl_idx; }
           62  +  catchsql {
           63  +    DROP TABLE rlog;
           64  +    DROP TABLE clog;
           65  +    DROP TABLE tbl;
           66  +    DROP TABLE other_tbl;
           67  +  }
           68  +
           69  +  execsql $tbl_defn
           70  +
           71  +  execsql {
           72  +    INSERT INTO tbl VALUES(1, 2);
           73  +    INSERT INTO tbl VALUES(3, 4);
           74  +
           75  +    CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
           76  +    CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
           77  +
           78  +    CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 
           79  +      BEGIN
           80  +      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
           81  +	  old.a, old.b, 
           82  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
           83  +	  new.a, new.b);
           84  +    END;
           85  +
           86  +    CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 
           87  +      BEGIN
           88  +      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
           89  +	  old.a, old.b, 
           90  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
           91  +	  new.a, new.b);
           92  +    END;
           93  +
           94  +    CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
           95  +      WHEN old.a = 1
           96  +      BEGIN
           97  +      INSERT INTO clog VALUES ( (SELECT max(idx) + 1 FROM clog), 
           98  +	  old.a, old.b, 
           99  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
          100  +	  new.a, new.b);
          101  +    END;
          102  +  }
          103  +
          104  +  do_test trig-1.1.$ii {
          105  +    execsql {
          106  +      UPDATE tbl SET a = a * 10, b = b * 10;
          107  +      SELECT * FROM rlog ORDER BY idx;
          108  +      SELECT * FROM clog ORDER BY idx;
          109  +    }
          110  +  } [list 1 1 2  4  6 10 20 \
          111  +          2 1 2 13 24 10 20 \
          112  +	  3 3 4 13 24 30 40 \
          113  +	  4 3 4 40 60 30 40 \
          114  +          1 1 2 13 24 10 20 ]
          115  +  
          116  +  execsql {
          117  +    DELETE FROM rlog;
          118  +    DELETE FROM tbl;
          119  +    INSERT INTO tbl VALUES (100, 100);
          120  +    INSERT INTO tbl VALUES (300, 200);
          121  +    CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
          122  +      BEGIN
          123  +      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
          124  +	  old.a, old.b, 
          125  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
          126  +	  0, 0);
          127  +    END;
          128  +
          129  +    CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
          130  +      BEGIN
          131  +      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
          132  +	  old.a, old.b, 
          133  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
          134  +	  0, 0);
          135  +    END;
          136  +  }
          137  +  do_test trig-1.2.$ii {
          138  +    execsql {
          139  +      DELETE FROM tbl;
          140  +      SELECT * FROM rlog;
          141  +    }
          142  +  } [list 1 100 100 400 300 0 0 \
          143  +          2 100 100 300 200 0 0 \
          144  +          3 300 200 300 200 0 0 \
          145  +          4 300 200 0 0 0 0 ]
          146  +
          147  +  execsql {
          148  +    DELETE FROM rlog;
          149  +    CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
          150  +      BEGIN
          151  +      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
          152  +	  0, 0,
          153  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
          154  +	  new.a, new.b);
          155  +    END;
          156  +
          157  +    CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
          158  +      BEGIN
          159  +      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
          160  +	  0, 0,
          161  +	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
          162  +	  new.a, new.b);
          163  +    END;
          164  +  }
          165  +  do_test trig-1.3.$ii {
          166  +    execsql {
          167  +
          168  +      CREATE TABLE other_tbl(a, b);
          169  +      INSERT INTO other_tbl VALUES(1, 2);
          170  +      INSERT INTO other_tbl VALUES(3, 4);
          171  +      -- INSERT INTO tbl SELECT * FROM other_tbl;
          172  +      INSERT INTO tbl VALUES(5, 6);
          173  +      DROP TABLE other_tbl;
          174  +
          175  +      SELECT * FROM rlog;
          176  +    }
          177  +  } [list 1 0 0 0 0 5 6 \
          178  +          2 0 0 5 6 5 6 ]
          179  +}
          180  +catchsql {
          181  +  DROP TABLE rlog;
          182  +  DROP TABLE clog;
          183  +  DROP TABLE tbl;
          184  +  DROP TABLE other_tbl;
          185  +}
          186  +
          187  +# 2.
          188  +set ii 0
          189  +foreach tr_program [ list \
          190  +   {UPDATE tbl SET b = old.b;} \
          191  +  {INSERT INTO log VALUES(new.c, 2, 3);} \
          192  +  {DELETE FROM log WHERE a = 1;} \
          193  +  {INSERT INTO tbl VALUES(500, new.b * 10, 700); 
          194  +    UPDATE tbl SET c = old.c; 
          195  +    DELETE FROM log;} \
          196  +  {INSERT INTO log select * from tbl;} 
          197  +   ] \
          198  +{
          199  +  foreach test_varset [ list \
          200  +    {
          201  +      set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 
          202  +      set prep      {INSERT INTO tbl VALUES(1, 2, 3);}
          203  +      set newC 10
          204  +      set newB 2
          205  +      set newA 1
          206  +      set oldA 1
          207  +      set oldB 2
          208  +      set oldC 3
          209  +    } \
          210  +    {
          211  +      set statement {DELETE FROM tbl WHERE a = 1;}
          212  +      set prep      {INSERT INTO tbl VALUES(1, 2, 3);}
          213  +      set oldA 1
          214  +      set oldB 2
          215  +      set oldC 3
          216  +    } \
          217  +    {
          218  +      set statement {INSERT INTO tbl VALUES(1, 2, 3);}
          219  +      set newA 1
          220  +      set newB 2
          221  +      set newC 3
          222  +    }
          223  +  ] \
          224  +  {
          225  +    set statement {}
          226  +    set prep {}
          227  +    set newA {''}
          228  +    set newB {''}
          229  +    set newC {''}
          230  +    set oldA {''}
          231  +    set oldB {''}
          232  +    set oldC {''}
          233  +
          234  +    incr ii
          235  +
          236  +    eval $test_varset
          237  +
          238  +    set statement_type [string range $statement 0 5]
          239  +    set tr_program_fixed $tr_program
          240  +    if {$statement_type == "DELETE"} {
          241  +      regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 
          242  +      regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 
          243  +      regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 
          244  +    }
          245  +    if {$statement_type == "INSERT"} {
          246  +      regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 
          247  +      regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 
          248  +      regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 
          249  +    }
          250  +
          251  +
          252  +    set tr_program_cooked $tr_program
          253  +    regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 
          254  +    regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 
          255  +    regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 
          256  +    regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 
          257  +    regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 
          258  +    regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 
          259  +
          260  +    catchsql {
          261  +      DROP TABLE tbl;
          262  +      DROP TABLE log;
          263  +    }
          264  +    execsql {
          265  +      CREATE TABLE tbl(a PRIMARY KEY, b, c);
          266  +      CREATE TABLE log(a, b, c);
          267  +    }
          268  +
          269  +    set query {SELECT * FROM tbl; SELECT * FROM log;}
          270  +    set prep "$prep; INSERT INTO log VALUES(1, 2, 3); INSERT INTO log VALUES(10, 20, 30);"
          271  +
          272  +# Check execution of BEFORE programs:
          273  +
          274  +    set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]
          275  +
          276  +    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
          277  +    execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"
          278  +
          279  +    do_test trig-2-$ii-before "execsql {$statement $query}" $before_data
          280  +
          281  +    execsql "DROP TRIGGER the_trigger;"
          282  +    execsql "DELETE FROM tbl; DELETE FROM log;"
          283  +
          284  +# Check execution of AFTER programs
          285  +    set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]
          286  +
          287  +    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
          288  +
          289  +    execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"
          290  +
          291  +    do_test trig-2-$ii-after "execsql {$statement $query}" $after_data
          292  +    execsql "DROP TRIGGER the_trigger;"
          293  +  }
          294  +}
          295  +catchsql {
          296  +  DROP TABLE tbl;
          297  +  DROP TABLE log;
          298  +}
          299  +
          300  +# 3.
          301  +
          302  +# trig-3.1: UPDATE OF triggers
          303  +execsql {
          304  +  CREATE TABLE tbl (a, b, c, d);
          305  +  CREATE TABLE log (a);
          306  +  INSERT INTO log VALUES (0);
          307  +  INSERT INTO tbl VALUES (0, 0, 0, 0);
          308  +  INSERT INTO tbl VALUES (1, 0, 0, 0);
          309  +  CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
          310  +    BEGIN
          311  +      UPDATE log SET a = a + 1;
          312  +    END;
          313  +}
          314  +do_test trig-3.1 {
          315  +  execsql {
          316  +    UPDATE tbl SET b = 1, c = 10; -- 2
          317  +    UPDATE tbl SET b = 10; -- 0
          318  +    UPDATE tbl SET d = 4 WHERE a = 0; --1
          319  +    UPDATE tbl SET a = 4, b = 10; --0
          320  +    SELECT * FROM log;
          321  +  }
          322  +} {3}
          323  +execsql {
          324  +  DROP TABLE tbl;
          325  +  DROP TABLE log;
          326  +}
          327  +
          328  +# trig-3.2: WHEN clause
          329  +set when_triggers [ list \
          330  +             {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
          331  +             {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ]
          332  +
          333  +execsql {
          334  +  CREATE TABLE tbl (a, b, c, d);
          335  +  CREATE TABLE log (a);
          336  +  INSERT INTO log VALUES (0);
          337  +}
          338  +
          339  +foreach trig $when_triggers {
          340  +  execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
          341  +}
          342  +
          343  +do_test trig-3.2 {
          344  +  execsql { 
          345  +
          346  +    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1
          347  +    SELECT * FROM log;
          348  +    UPDATE log SET a = 0;
          349  +
          350  +    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0
          351  +    SELECT * FROM log;
          352  +    UPDATE log SET a = 0;
          353  +
          354  +    INSERT INTO tbl VALUES(200, 0, 0, 0);     -- 1
          355  +    SELECT * FROM log;
          356  +    UPDATE log SET a = 0;
          357  +  }
          358  +} {1 0 1}
          359  +execsql {
          360  +  DROP TABLE tbl;
          361  +  DROP TABLE log;
          362  +}
          363  +
          364  +# Simple cascaded trigger
          365  +execsql {
          366  +  CREATE TABLE tblA(a, b);
          367  +  CREATE TABLE tblB(a, b);
          368  +  CREATE TABLE tblC(a, b);
          369  +
          370  +  CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN
          371  +    INSERT INTO tblB values(new.a, new.b);
          372  +  END;
          373  +
          374  +  CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
          375  +    INSERT INTO tblC values(new.a, new.b);
          376  +  END;
          377  +}
          378  +do_test trig-4.1 {
          379  +  execsql {
          380  +    INSERT INTO tblA values(1, 2);
          381  +    SELECT * FROM tblA;
          382  +    SELECT * FROM tblB;
          383  +    SELECT * FROM tblC;
          384  +  }
          385  +} {1 2 1 2 1 2}
          386  +execsql {
          387  +  DROP TABLE tblA;
          388  +  DROP TABLE tblB;
          389  +  DROP TABLE tblC;
          390  +}
          391  +
          392  +# Simple recursive trigger
          393  +execsql {
          394  +  CREATE TABLE tbl(a, b, c);
          395  +  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
          396  +    BEGIN
          397  +      INSERT INTO tbl VALUES (new.a, new.b, new.c);
          398  +    END;
          399  +}
          400  +do_test trig-4.2 {
          401  +  execsql {
          402  +    INSERT INTO tbl VALUES (1, 2, 3);
          403  +    select * from tbl;
          404  +  }
          405  +} {1 2 3 1 2 3}
          406  +execsql {
          407  +  DROP TABLE tbl;
          408  +}
          409  +
          410  +# 5.
          411  +execsql {
          412  +  CREATE TABLE tbl(a, b, c);
          413  +  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
          414  +    BEGIN
          415  +      INSERT INTO tbl VALUES (1, 2, 3);
          416  +      INSERT INTO tbl VALUES (2, 2, 3);
          417  +      UPDATE tbl set b = 10 WHERE a = 1;
          418  +      DELETE FROM tbl WHERE a = 1;
          419  +      DELETE FROM tbl;
          420  +    END;
          421  +}
          422  +do_test trig-5 {
          423  +  execsql {
          424  +    INSERT INTO tbl VALUES(100, 200, 300);
          425  +  }
          426  +  db changes
          427  +} {1}
          428  +execsql {
          429  +  DROP TABLE tbl;
          430  +}
          431  +
          432  +
          433  +# Handling of ON CONFLICT by INSERT statements inside triggers
          434  +execsql {
          435  +  CREATE TABLE tbl (a primary key, b, c);
          436  +  CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
          437  +    INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
          438  +  END;
          439  +}
          440  +do_test trig-6.1a {
          441  +  execsql {
          442  +    BEGIN;
          443  +    INSERT INTO tbl values (1, 2, 3);
          444  +    SELECT * from tbl;
          445  +  }
          446  +} {1 2 3}
          447  +do_test trig-6.1b {
          448  +  catchsql {
          449  +    INSERT OR ABORT INTO tbl values (2, 2, 3);
          450  +  }
          451  +} {1 {constraint failed}}
          452  +do_test trig-6.1c {
          453  +  execsql {
          454  +    SELECT * from tbl;
          455  +  }
          456  +} {1 2 3}
          457  +do_test trig-6.1d {
          458  +  catchsql {
          459  +    INSERT OR FAIL INTO tbl values (2, 2, 3);
          460  +  }
          461  +} {1 {constraint failed}}
          462  +do_test trig-6.1e {
          463  +  execsql {
          464  +    SELECT * from tbl;
          465  +  }
          466  +} {1 2 3 2 2 3}
          467  +do_test trig-6.1f {
          468  +  execsql {
          469  +    INSERT OR REPLACE INTO tbl values (2, 2, 3);
          470  +    SELECT * from tbl;
          471  +  }
          472  +} {1 2 3 2 0 0}
          473  +do_test trig-6.1g {
          474  +  catchsql {
          475  +    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
          476  +  }
          477  +} {1 {constraint failed}}
          478  +do_test trig-6.1h {
          479  +  execsql {
          480  +    SELECT * from tbl;
          481  +  }
          482  +} {}
          483  +
          484  +
          485  +# Handling of ON CONFLICT by UPDATE statements inside triggers
          486  +execsql {
          487  +  INSERT INTO tbl values (4, 2, 3);
          488  +  INSERT INTO tbl values (6, 3, 4);
          489  +  CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
          490  +    UPDATE OR IGNORE tbl SET a = new.a, c = 10;
          491  +  END;
          492  +}
          493  +do_test trig-6.2a {
          494  +  execsql {
          495  +    BEGIN;
          496  +    UPDATE tbl SET a = 1 WHERE a = 4;
          497  +    SELECT * from tbl;
          498  +  }
          499  +} {1 2 10 6 3 4}
          500  +do_test trig-6.2b {
          501  +  catchsql {
          502  +    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
          503  +  }
          504  +} {1 {constraint failed}}
          505  +do_test trig-6.2c {
          506  +  execsql {
          507  +    SELECT * from tbl;
          508  +  }
          509  +} {1 2 10 6 3 4}
          510  +do_test trig-6.2d {
          511  +  catchsql {
          512  +    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
          513  +  }
          514  +} {1 {constraint failed}}
          515  +do_test trig-6.2e {
          516  +  execsql {
          517  +    SELECT * from tbl;
          518  +  }
          519  +} {4 2 10 6 3 4}
          520  +do_test trig-6.2f {
          521  +  execsql {
          522  +    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
          523  +    SELECT * from tbl;
          524  +  }
          525  +} {1 3 10}
          526  +execsql {
          527  +  INSERT INTO tbl VALUES (2, 3, 4);
          528  +}
          529  +do_test trig-6.2g {
          530  +  catchsql {
          531  +    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
          532  +  }
          533  +} {1 {constraint failed}}
          534  +do_test trig-6.2h {
          535  +  execsql {
          536  +    SELECT * from tbl;
          537  +  }
          538  +} {4 2 3 6 3 4}
          539  +execsql {
          540  +  DROP TABLE tbl;
          541  +}
          542  +
          543  +# 7. Triggers on views
          544  +execsql {
          545  +  CREATE TABLE ab(a, b);
          546  +  CREATE TABLE cd(c, d);
          547  +  INSERT INTO ab VALUES (1, 2);
          548  +  INSERT INTO ab VALUES (0, 0);
          549  +  INSERT INTO cd VALUES (3, 4);
          550  +
          551  +  CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 
          552  +      olda, oldb, oldc, oldd, newa, newb, newc, newd);
          553  +
          554  +  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;
          555  +
          556  +  CREATE TRIGGER before_update BEFORE UPDATE ON abcd BEGIN
          557  +    INSERT INTO tlog VALUES(NULL, 
          558  +	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
          559  +  END;
          560  +  CREATE TRIGGER after_update AFTER UPDATE ON abcd BEGIN
          561  +    INSERT INTO tlog VALUES(NULL, 
          562  +	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
          563  +  END;
          564  +
          565  +  CREATE TRIGGER before_delete BEFORE DELETE ON abcd BEGIN
          566  +    INSERT INTO tlog VALUES(NULL, 
          567  +	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
          568  +  END;
          569  +  CREATE TRIGGER after_delete AFTER DELETE ON abcd BEGIN
          570  +    INSERT INTO tlog VALUES(NULL, 
          571  +	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
          572  +  END;
          573  +
          574  +  CREATE TRIGGER before_insert BEFORE INSERT ON abcd BEGIN
          575  +    INSERT INTO tlog VALUES(NULL, 
          576  +	0, 0, 0, 0, new.a, new.b, new.c, new.d);
          577  +  END;
          578  +   CREATE TRIGGER after_insert AFTER INSERT ON abcd BEGIN
          579  +    INSERT INTO tlog VALUES(NULL, 
          580  +	0, 0, 0, 0, new.a, new.b, new.c, new.d);
          581  +   END;
          582  +}
          583  +
          584  +do_test trig-7 {
          585  +  execsql {
          586  +    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
          587  +    DELETE FROM abcd WHERE a = 1;
          588  +    INSERT INTO abcd VALUES(10, 20, 30, 40);
          589  +    SELECT * FROM tlog;
          590  +  }
          591  +} [ list 1 1 2 3 4 100 25 3 4 \
          592  +         2 1 2 3 4 100 25 3 4 \
          593  + 3 1 2 3 4 0 0 0 0 4 1 2 3 4 0 0 0 0 \
          594  + 5 0 0 0 0 10 20 30 40 6 0 0 0 0 10 20 30 40 ]
          595  +
          596  +finish_test
          597  +

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.33 2002/05/06 11:47:33 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.34 2002/05/15 08:30:15 danielk1977 Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
    50     50     {EXPLAIN explain}
    51     51     {expression expr}
    52     52     {{BEGIN TRANSACTION} transaction}
    53     53     {PRAGMA pragma}
    54     54     {{ON CONFLICT clause} conflict}
    55     55     {{CREATE VIEW} createview}
    56     56     {{DROP VIEW} dropview}
           57  +  {{CREATE TRIGGER} createtrigger}
           58  +  {{DROP TRIGGER} droptrigger}
    57     59   }] {
    58     60     puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
    59     61   }
    60     62   puts {</ul></p>
    61     63   
    62     64   <p>Details on the implementation of each command are provided in
    63     65   the sequel.</p>
................................................................................
  1084   1086   table or index then it is suppose to clean up the named table or index.
  1085   1087   In version 1.0 of SQLite, the VACUUM command would invoke 
  1086   1088   <b>gdbm_reorganize()</b> to clean up the backend database file.
  1087   1089   Beginning with version 2.0 of SQLite, GDBM is no longer used for
  1088   1090   the database backend and VACUUM has become a no-op.
  1089   1091   </p>
  1090   1092   }
         1093  +
         1094  +Section {CREATE TRIGGER} createtrigger
         1095  +
         1096  +Syntax {sql-statement} {
         1097  +CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ]
         1098  +<database-event>
         1099  +<trigger-action>
         1100  +}
         1101  +
         1102  +Syntax {database-event} {
         1103  +DELETE | 
         1104  +INSERT | 
         1105  +UPDATE | 
         1106  +UPDATE OF <column-list>
         1107  +ON <table-name> 
         1108  +}
         1109  +
         1110  +Syntax {trigger-action} {
         1111  +[ FOR EACH ROW ] [ WHEN <expression> ] 
         1112  +BEGIN 
         1113  +  <trigger-step> ; [ <trigger-step> ; ]*
         1114  +END
         1115  +}
         1116  +
         1117  +Syntax {trigger-step} {
         1118  +<update-statement> | <insert-statement> | 
         1119  +<delete-statement> | <select-statement> 
         1120  +}
         1121  +
         1122  +puts {
         1123  +<p>The CREATE TRIGGER statement is used to add triggers to the 
         1124  +database schema. Triggers are database operations (the <i>trigger-action</i>) 
         1125  +that are automatically performed when a specified database event (the
         1126  +<i>database-event</i>) occurs.  </p>
         1127  +
         1128  +<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a
         1129  +particular database table occurs, or whenever an UPDATE of one or more
         1130  +specified columns of a table are updated.</p>
         1131  +
         1132  +<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
         1133  +STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
         1134  +EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
         1135  +may be executed (depending on the WHEN clause) for each database row being
         1136  +inserted, updated or deleted by the statement causing the trigger to fire.</p>
         1137  +
         1138  +<p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of 
         1139  +the row being inserted, deleted or updated using references of the form 
         1140  +"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where
         1141  +<i>column-name</i> is the name of a column from the table that the trigger
         1142  +is associated with. OLD and NEW references may only be used in triggers on
         1143  +<i>trigger-event</i>s for which they are relevant, as follows:</p>
         1144  +
         1145  +<table border=0 cellpadding=10>
         1146  +<tr>
         1147  +<td valign="top" align="right" width=120><i>INSERT</i></td>
         1148  +<td valign="top">NEW references are valid</td>
         1149  +</tr>
         1150  +<tr>
         1151  +<td valign="top" align="right" width=120><i>UPDATE</i></td>
         1152  +<td valign="top">NEW and OLD references are valid</td>
         1153  +</tr>
         1154  +<tr>
         1155  +<td valign="top" align="right" width=120><i>DELETE</i></td>
         1156  +<td valign="top">OLD references are valid</td>
         1157  +</tr>
         1158  +</table>
         1159  +</p>
         1160  +
         1161  +<p>If a WHEN clause is supplied, the SQL statements specified as <i>trigger-steps</i> are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.</p>
         1162  +
         1163  +<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
         1164  +will be executed relative to the insertion, modification or removal of the
         1165  +associated row.</p>
         1166  +
         1167  +<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
         1168  +<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
         1169  +the statement causing the trigger to fire, then this conflict handling
         1170  +policy is used instead.</p>
         1171  +
         1172  +<p>Triggers are automatically dropped when the table that they are 
         1173  +associated with is dropped.</p>
         1174  +
         1175  +<p>Triggers may be created on views, as well as ordinary tables. If one or
         1176  +more INSERT, DELETE or UPDATE triggers are defined on a view, then it is not
         1177  +an error to execute an INSERT, DELETE or UPDATE statement on the view, 
         1178  +respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view
         1179  +causes the associated triggers to fire. The real tables underlying the view
         1180  +are not modified (except possibly explicitly, by a trigger program).</p>
         1181  +
         1182  +<p><b>Example:</b></p>
         1183  +
         1184  +<p>Assuming that customer records are stored in the "customers" table, and
         1185  +that order records are stored in the "orders" table, the following trigger
         1186  +ensures that all associated orders are redirected when a customer changes
         1187  +his or her address:</p>
         1188  +}
         1189  +Example {
         1190  +CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
         1191  +  BEGIN
         1192  +    UPDATE orders SET address = new.address WHERE customer_name = old.name;
         1193  +  END;
         1194  +}
         1195  +puts {
         1196  +<p>With this trigger installed, executing the statement:</p>
         1197  +}
         1198  +Example {
         1199  +UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
         1200  +}
         1201  +puts {
         1202  +<p>causes the following to be automatically executed:</p>
         1203  +}
         1204  +Example {
         1205  +UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
         1206  +}
         1207  +
         1208  +Section {DROP TRIGGER} droptrigger
         1209  +Syntax {sql-statement} {
         1210  +DROP TRIGGER <trigger-name>
         1211  +}
         1212  +puts { 
         1213  +  <p>Used to drop a trigger from the database schema. Note that triggers
         1214  +  are automatically dropped when the associated table is dropped.</p>
         1215  +}
  1091   1216   
  1092   1217   
  1093   1218   puts {
  1094   1219   <p><hr /></p>
  1095   1220   <p><a href="index.html"><img src="/goback.jpg" border=0 />
  1096   1221   Back to the SQLite Home Page</a>
  1097   1222   </p>
  1098   1223   
  1099   1224   </body></html>}