SQLite4
Check-in [77b33bff0a]
Not logged in

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

Overview
Comment:Fix more problems with triggers and triggers on views.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | primary-keys
Files: files | file ages | folders
SHA1: 77b33bff0a690af851cf71e198fb77ae0f28b6e7
User & Date: dan 2012-04-17 08:23:02
Context
2012-04-17
09:10
Enforce NOT NULL on all PRIMARY KEY columns. check-in: 035fdd3f5e user: dan tags: primary-keys
08:23
Fix more problems with triggers and triggers on views. check-in: 77b33bff0a user: dan tags: primary-keys
05:36
Fix an issue with sub-transaction rollback. check-in: 123a055a36 user: dan tags: primary-keys
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578


1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
  Token *pEnd,            /* The final ')' token in the CREATE TABLE */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;
  sqlite4 *db = pParse->db;
  int iDb;
  int iPkRoot = 0;                /* Root page of primary key index */
  Index *pPk;                     /* PRIMARY KEY index for table p */

  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
    return;
  }
  p = pParse->pNewTable;
  if( p==0 ) return;

  assert( !db->init.busy || !pSelect );
  iDb = sqlite4SchemaToIndex(db, p->pSchema);



  if( 0==(p->tabFlags & TF_HasPrimaryKey) ){
    /* If no explicit PRIMARY KEY has been created, add an implicit 
    ** primary key here.  An implicit primary key works the way "rowid" did
    ** in SQLite 3.  */
    addImplicitPrimaryKey(pParse, p, iDb);
  }
  pPk = sqlite4FindPrimaryKey(p, 0);
  assert( pPk || pParse->nErr || db->mallocFailed );
  if( pPk ) iPkRoot = pPk->tnum;


#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
    NameContext sNC;                /* Name context for pParse->pNewTable */







<










>
>
|
|
|
|
|
|
|
|
|
|







1561
1562
1563
1564
1565
1566
1567

1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
  Token *pEnd,            /* The final ')' token in the CREATE TABLE */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;
  sqlite4 *db = pParse->db;
  int iDb;
  int iPkRoot = 0;                /* Root page of primary key index */


  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
    return;
  }
  p = pParse->pNewTable;
  if( p==0 ) return;

  assert( !db->init.busy || !pSelect );
  iDb = sqlite4SchemaToIndex(db, p->pSchema);

  if( !IsView(p) ){
    Index *pPk;                   /* PRIMARY KEY index of table p */
    if( 0==(p->tabFlags & TF_HasPrimaryKey) ){
      /* If no explicit PRIMARY KEY has been created, add an implicit 
      ** primary key here.  An implicit primary key works the way "rowid" 
      ** did in SQLite 3.  */
      addImplicitPrimaryKey(pParse, p, iDb);
    }
    pPk = sqlite4FindPrimaryKey(p, 0);
    assert( pPk || pParse->nErr || db->mallocFailed );
    if( pPk ) iPkRoot = pPk->tnum;
  }

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
    NameContext sNC;                /* Name context for pParse->pNewTable */

Changes to src/delete.c.

220
221
222
223
224
225
226

227
228

229
230
231
232
233
234
235
236
237
238
239
240
241
242

243
244
245
246


247
248
249
250
251
252
253
254
255
256
257
258



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281



282
283


284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321

322
323
324
325
326
327
328
329
330

331
332
333
334
335
336
337
...
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355


356
357
358
359
360
361
362
...
371
372
373
374
375
376
377
378

379
380
381
382
383
384
385
386
...
388
389
390
391
392
393
394
395
396
397

398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
...
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507

508
509
510
511
512
513
514

515
516
517
518
519
520
521
522
...
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
**                  pTabList              pWhere
*/
void sqlite4DeleteFrom(
  Parse *pParse,         /* The parser context */
  SrcList *pTabList,     /* The table from which we should delete things */
  Expr *pWhere           /* The WHERE clause.  May be null */
){

  Vdbe *v;               /* The virtual database engine */
  Table *pTab;           /* The table from which records will be deleted */

  const char *zDb;       /* Name of database holding pTab */
  int addr = 0;     /* A couple addresses of generated code */
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int iCur;              /* VDBE Cursor number for pTab */
  sqlite4 *db;           /* Main database structure */
  AuthContext sContext;  /* Authorization context */
  NameContext sNC;       /* Name context to resolve expressions in */
  int iDb;               /* Database number */
  int rcauth;            /* Value returned by authorization callback */

#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* True if attempting to delete from a view */

  Trigger *pTrigger;           /* List of table triggers, if required */
#endif

  memset(&sContext, 0, sizeof(sContext));


  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto delete_from_cleanup;
  }
  assert( pTabList->nSrc==1 );

  /* Locate the table which we want to delete.  This table has to be
  ** put in an SrcList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an SrcList* parameter instead of just a Table* parameter.  */
  pTab = sqlite4SrcListLookup(pParse, pTabList);
  if( pTab==0 )  goto delete_from_cleanup;




  /* Figure out if we have any triggers and if the table being
  ** deleted from is a view.  */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  isView = pTab->pSelect!=0;
#else
# define pTrigger 0
# define isView 0
#endif
#ifdef SQLITE_OMIT_VIEW
# undef isView
# define isView 0
#endif

  /* If pTab is really a view, make sure it has been initialized. */
  if( sqlite4ViewGetColumnNames(pParse, pTab) ){
    goto delete_from_cleanup;
  }

  /* Check the table is not read-only (e.g. sqlite_master or sqlite_stat).
  ** Also, check that if pTab is really an SQL view, one or more INSTEAD 
  ** OF DELETE triggers have been configured.  */



  if( sqlite4IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
    goto delete_from_cleanup;


  }
  assert(!isView || pTrigger);

  /* Invoke the authorization callback */
  iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
  assert( iDb<db->nDb );
  zDb = db->aDb[iDb].zName;
  rcauth = sqlite4AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb);
  assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
  if( rcauth==SQLITE_DENY ){
    goto delete_from_cleanup;
  }

  /* Assign cursor numbers to each of the tables indexes. */
  assert( pTabList->nSrc==1 );
  iCur = pTabList->a[0].iCursor = pParse->nTab++;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }

  /* Start the view context
  */
  if( isView ){
    sqlite4AuthContextPush(pParse, &sContext, pTab->zName);
  }

  /* Begin generating code.
  */
  v = sqlite4GetVdbe(pParse);
  if( v==0 ){
    goto delete_from_cleanup;
  }
  if( pParse->nested==0 ) sqlite4VdbeCountChanges(v);
  sqlite4BeginWriteOperation(pParse, 1, iDb);

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */

#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  if( isView ){
    sqlite4MaterializeView(pParse, pTab, pWhere, iCur);
  }
#endif

  /* Resolve the column names in the WHERE clause.
  */
  memset(&sNC, 0, sizeof(sNC));

  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  if( sqlite4ResolveExprNames(&sNC, pWhere) ){
    goto delete_from_cleanup;
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
................................................................................
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)
  ){
    assert( !isView );
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  /* The usual case: There is a WHERE clause so we have to scan through
  ** the table and pick which records to delete.
  */
  {


    int regSet = ++pParse->nMem;  /* Register for rowset of rows to delete */
    int regKey = ++pParse->nMem;  /* Used for storing row keys */
    int addrTop;                  /* Instruction (KeySetRead) at top of loop */

    /* Query the table for all rows that match the WHERE clause. Store the
    ** PRIMARY KEY for each matching row in the KeySet object in register
    ** regSet. After the scan is complete, the VM will loop through the set 
................................................................................
    if( pWInfo==0 ) goto delete_from_cleanup;
    sqlite4VdbeAddOp2(v, OP_RowKey, iCur, regKey);
    sqlite4VdbeAddOp2(v, OP_KeySetAdd, regSet, regKey);
    sqlite4WhereEnd(pWInfo);

    /* Unless this is a view, open cursors for all indexes on the table
    ** from which we are deleting.  */
    if( !isView ){

      sqlite4OpenAllIndexes(pParse, pTab, iCur, OP_OpenWrite);
    }

    addrTop = sqlite4VdbeAddOp3(v, OP_KeySetRead, regSet, 0, regKey);

    /* Delete the row */
#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( IsVirtual(pTab) ){
................................................................................
      sqlite4VtabMakeWritable(pParse, pTab);
      sqlite4VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB);
      sqlite4VdbeChangeP5(v, OE_Abort);
      sqlite4MayAbort(pParse);
    }else
#endif
    {
      int count = (pParse->nested==0);    /* True to count changes */
      sqlite4GenerateRowDelete(
          pParse, pTab, iCur, regKey, count, pTrigger, OE_Default

      );
    }

    /* End of the delete loop */
    sqlite4VdbeAddOp2(v, OP_Goto, 0, addrTop);
    sqlite4VdbeJumpHere(v, addrTop);

    /* Close all open cursors */
    sqlite4CloseAllIndexes(pParse, pTab, iCur);
  }

  /* Update the sqlite_sequence table by storing the content of the
  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->pTriggerTab==0 ){
................................................................................

delete_from_cleanup:
  sqlite4AuthContextPop(&sContext);
  sqlite4SrcListDelete(db, pTabList);
  sqlite4ExprDelete(db, pWhere);
  return;
}
/* Make sure "isView" and other macros defined above are undefined. Otherwise
** thely may interfere with compilation of other functions in this file
** (or in another file, if this file becomes part of the amalgamation).  */
#ifdef isView
 #undef isView
#endif
#ifdef pTrigger
 #undef pTrigger
#endif

/*
** This routine generates VDBE code that causes a single row of a
** single table to be deleted.
**
** The VDBE must be in a particular state when this routine is called.
** These are the requirements:
................................................................................
    ** Or, if the table contains more than 32 columns and at least one of
    ** the columns following the 32nd is required, set mask to 0xffffffff.  */
    mask = sqlite4TriggerColmask(
        pParse, pTrigger, 0, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onconf
    );
    mask |= sqlite4FkOldmask(pParse, pTab);

    /* Allocate an array of registers - one for each column in the table.
    ** Then populate those array elements that may be used by FK or trigger
    ** logic with the OLD.* values.
    **
    ** The array is (nCol+1) registers in size, where nCol is the number of
    ** columns in the table. If the table has an implicit PK, the first 
    ** register in the array contains the rowid. Otherwise, its contents are
    ** undefined.  
    */

    regOld = pParse->nMem+1;
    pParse->nMem += (pTab->nCol+1);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( mask==0xffffffff || mask&(1<<iCol) ){
        sqlite4ExprCodeGetColumnOfTable(v, pTab, iPkCsr, iCol, regOld+iCol+1);
      }
    }

    if( pPk->aiColumn[0]<0 ){
      sqlite4VdbeAddOp2(v, OP_Rowid, iPkCsr, regOld);
    }

    /* Invoke BEFORE DELETE trigger programs. */
    sqlite4CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, regOld, onconf, iLabel
    );
................................................................................
    ** are not violated by deleting this row.  */
    sqlite4FkCheck(pParse, pTab, regOld+1, 0);
  }

  /* Delete the index and table entries. Skip this step if pTab is really
  ** a view (in which case the only effect of the DELETE statement is to
  ** fire the INSTEAD OF triggers).  */ 
  if( pTab->pSelect==0 ){
    sqlite4GenerateRowIndexDelete(pParse, pTab, baseCur, 0);
#if 0
    if( count ){
      sqlite4VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT);
    }
#endif
  }

  /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
  ** handle rows (possibly in other tables) that refer via a foreign key
  ** to the row just deleted. This is a no-op if there are no configured
  ** foreign keys that use this table as a parent table.  */ 
  sqlite4FkActions(pParse, pTab, 0, regOld+1);







>
|
<
>
|
<
<
<
<
<
<
|
|
|
|
<
<
<
>
|
<


>
>






|
|
<
<


>
>
>

<
|
<

<
<
<
<
<
<
<
<
<


|
<
|
<
|
<
<
>
>
>
|
<
>
>
|
<
<

<
<
<






|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<
<
<
<




|
<
>
|
<


<

|
<
<
>







 







|










>
>







 







|
>
|







 







<

<
>








|







 







<
<
<
<
<
<
<
<
<







 







|
|
<

<
|
|
<
<
>







>
|







 







|

<
<
<
<
<







220
221
222
223
224
225
226
227
228

229
230






231
232
233
234



235
236

237
238
239
240
241
242
243
244
245
246
247
248


249
250
251
252
253
254

255

256









257
258
259

260

261


262
263
264
265

266
267
268


269



270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289





290
291
292
293
294

295
296

297
298

299
300


301
302
303
304
305
306
307
308
...
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
...
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
...
362
363
364
365
366
367
368

369

370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
...
389
390
391
392
393
394
395









396
397
398
399
400
401
402
...
456
457
458
459
460
461
462
463
464

465

466
467


468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
...
494
495
496
497
498
499
500
501
502





503
504
505
506
507
508
509
**                  pTabList              pWhere
*/
void sqlite4DeleteFrom(
  Parse *pParse,         /* The parser context */
  SrcList *pTabList,     /* The table from which we should delete things */
  Expr *pWhere           /* The WHERE clause.  May be null */
){
  sqlite4 *db = pParse->db;       /* Main database structure */
  Vdbe *v;                        /* The virtual database engine */

  Table *pTab;                    /* Table to delete from */
  const char *zDb;                /* Name of database holding pTab */






  AuthContext sContext;           /* Authorization context */
  NameContext sNC;                /* Name context to resolve WHERE expression */
  int iDb;                        /* Database number */
  int rcauth;                     /* Value returned by authorization callback */



  int iCur;                       /* Cursor number used by where.c */
  Trigger *pTrigger;              /* List of triggers, or NULL */


  memset(&sContext, 0, sizeof(sContext));
  memset(&sNC, 0, sizeof(sNC));

  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto delete_from_cleanup;
  }
  assert( pTabList->nSrc==1 );

  /* Locate the table which we want to delete. If it is a view, make sure
  ** that the column names are initialized. */


  pTab = sqlite4SrcListLookup(pParse, pTabList);
  if( pTab==0 )  goto delete_from_cleanup;
  iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
  zDb = db->aDb[iDb].zName;
  assert( iDb<db->nDb );


  /* Figure out if there are any triggers */

  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_DELETE, 0, 0);










  /* If pTab is really a view, make sure it has been initialized. */
  if( sqlite4ViewGetColumnNames(pParse, pTab) ) goto delete_from_cleanup;



  /* Check the table is not read-only. A table is read-only if it is one


  ** of the built-in system tables (e.g. sqlite_master, sqlite_stat) or
  ** if it is a view and there are no INSTEAD OF triggers to handle the 
  ** delete.  */
  if( sqlite4IsReadOnly(pParse, pTab, pTrigger!=0) ) goto delete_from_cleanup;

  assert( !IsView(pTab) || pTrigger );
  assert( !IsView(pTab) || pTab->pIndex==0 );



  /* Invoke the authorization callback */



  rcauth = sqlite4AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb);
  assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
  if( rcauth==SQLITE_DENY ){
    goto delete_from_cleanup;
  }

  /* Assign a cursor number to the table or view this statement is 
  ** deleting from. If pTab is actually a view, this will be used as the
  ** ephemeral table cursor. 
  **
  ** Or, if this is a real table, it is the number of a read-only cursor 
  ** used by where.c to iterate through those records that match the WHERE 
  ** clause supplied by the user. This is a separate cursor from the array
  ** of read-write cursors used to delete entries from each of the tables
  ** indexes.  */
  pTabList->a[0].iCursor = iCur = pParse->nTab++;

  /* Begin generating code */
  v = sqlite4GetVdbe(pParse);
  if( v==0 ) goto delete_from_cleanup;





  if( pParse->nested==0 ) sqlite4VdbeCountChanges(v);
  sqlite4BeginWriteOperation(pParse, 1, iDb);

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.  */

  if( IsView(pTab) ){
    sqlite4AuthContextPush(pParse, &sContext, pTab->zName);

    sqlite4MaterializeView(pParse, pTab, pWhere, iCur);
  }


  /* Resolve the column names in the WHERE clause. This has to come after


  ** the call to sqlite4MaterializeView() above.  */
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  if( sqlite4ResolveExprNames(&sNC, pWhere) ){
    goto delete_from_cleanup;
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
................................................................................
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)
  ){
    Index *pIdx;                  /* For looping over indices of the table */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  /* The usual case: There is a WHERE clause so we have to scan through
  ** the table and pick which records to delete.
  */
  {
    WhereInfo *pWInfo;            /* Information about the WHERE clause */
    int baseCur = 0;
    int regSet = ++pParse->nMem;  /* Register for rowset of rows to delete */
    int regKey = ++pParse->nMem;  /* Used for storing row keys */
    int addrTop;                  /* Instruction (KeySetRead) at top of loop */

    /* Query the table for all rows that match the WHERE clause. Store the
    ** PRIMARY KEY for each matching row in the KeySet object in register
    ** regSet. After the scan is complete, the VM will loop through the set 
................................................................................
    if( pWInfo==0 ) goto delete_from_cleanup;
    sqlite4VdbeAddOp2(v, OP_RowKey, iCur, regKey);
    sqlite4VdbeAddOp2(v, OP_KeySetAdd, regSet, regKey);
    sqlite4WhereEnd(pWInfo);

    /* Unless this is a view, open cursors for all indexes on the table
    ** from which we are deleting.  */
    if( !IsView(pTab) ){
      baseCur = pParse->nTab;
      sqlite4OpenAllIndexes(pParse, pTab, baseCur, OP_OpenWrite);
    }

    addrTop = sqlite4VdbeAddOp3(v, OP_KeySetRead, regSet, 0, regKey);

    /* Delete the row */
#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( IsVirtual(pTab) ){
................................................................................
      sqlite4VtabMakeWritable(pParse, pTab);
      sqlite4VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB);
      sqlite4VdbeChangeP5(v, OE_Abort);
      sqlite4MayAbort(pParse);
    }else
#endif
    {

      sqlite4GenerateRowDelete(

          pParse, pTab, baseCur, regKey, pParse->nested==0, pTrigger, OE_Default
      );
    }

    /* End of the delete loop */
    sqlite4VdbeAddOp2(v, OP_Goto, 0, addrTop);
    sqlite4VdbeJumpHere(v, addrTop);

    /* Close all open cursors */
    sqlite4CloseAllIndexes(pParse, pTab, baseCur);
  }

  /* Update the sqlite_sequence table by storing the content of the
  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->pTriggerTab==0 ){
................................................................................

delete_from_cleanup:
  sqlite4AuthContextPop(&sContext);
  sqlite4SrcListDelete(db, pTabList);
  sqlite4ExprDelete(db, pWhere);
  return;
}










/*
** This routine generates VDBE code that causes a single row of a
** single table to be deleted.
**
** The VDBE must be in a particular state when this routine is called.
** These are the requirements:
................................................................................
    ** Or, if the table contains more than 32 columns and at least one of
    ** the columns following the 32nd is required, set mask to 0xffffffff.  */
    mask = sqlite4TriggerColmask(
        pParse, pTrigger, 0, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onconf
    );
    mask |= sqlite4FkOldmask(pParse, pTab);

    /* Allocate an array of (nCol+1) registers, where nCol is the number
    ** of columns in the table. 

    **

    ** If the table has an implicit PK, the first register in the array 
    ** contains the rowid. Otherwise, its contents are undefined. The 


    ** remaining registers contain the OLD.* column values, in order. */
    regOld = pParse->nMem+1;
    pParse->nMem += (pTab->nCol+1);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( mask==0xffffffff || mask&(1<<iCol) ){
        sqlite4ExprCodeGetColumnOfTable(v, pTab, iPkCsr, iCol, regOld+iCol+1);
      }
    }
    assert( (pPk==0)==IsView(pTab) );
    if( pPk && pPk->aiColumn[0]<0 ){
      sqlite4VdbeAddOp2(v, OP_Rowid, iPkCsr, regOld);
    }

    /* Invoke BEFORE DELETE trigger programs. */
    sqlite4CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, regOld, onconf, iLabel
    );
................................................................................
    ** are not violated by deleting this row.  */
    sqlite4FkCheck(pParse, pTab, regOld+1, 0);
  }

  /* Delete the index and table entries. Skip this step if pTab is really
  ** a view (in which case the only effect of the DELETE statement is to
  ** fire the INSTEAD OF triggers).  */ 
  if( !IsView(pTab) ){
    sqlite4GenerateRowIndexDelete(pParse, pTab, baseCur, 0);





  }

  /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
  ** handle rows (possibly in other tables) that refer via a foreign key
  ** to the row just deleted. This is a no-op if there are no configured
  ** foreign keys that use this table as a parent table.  */ 
  sqlite4FkActions(pParse, pTab, 0, regOld+1);

Changes to src/insert.c.

565
566
567
568
569
570
571

572
573
574
575
576
577
578
579
....
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
  }

  /* Set bImplicitPK to true for an implicit PRIMARY KEY, or false otherwise.
  ** Also set pPk to point to the primary key, and iPk to the cursor offset
  ** of the primary key cursor (i.e. so that the cursor opened on the primary
  ** key index is VDBE cursor (baseCur+iPk).  */
  pPk = sqlite4FindPrimaryKey(pTab, &iPk);

  bImplicitPK = pPk->aiColumn[0]==-1;

  /* Figure out if we have any triggers and if the table being
  ** inserted into is a view. */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);
  isView = pTab->pSelect!=0;
#else
................................................................................
  int baseCur
){
  int i;
  Index *pIdx;
  Vdbe *v;

  assert( pTab->pIndex==0 || IsVirtual(pTab)==0 );
#ifndef SQLITE_OMIT_VIEW
  assert( pTab->pIndex==0 || pTab->pSelect==0 );
#endif

  v = sqlite4GetVdbe(pParse);
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    sqlite4VdbeAddOp1(v, OP_Close, baseCur+i);
  }
}








>
|







 







<
|
<







565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
....
1472
1473
1474
1475
1476
1477
1478

1479

1480
1481
1482
1483
1484
1485
1486
  }

  /* Set bImplicitPK to true for an implicit PRIMARY KEY, or false otherwise.
  ** Also set pPk to point to the primary key, and iPk to the cursor offset
  ** of the primary key cursor (i.e. so that the cursor opened on the primary
  ** key index is VDBE cursor (baseCur+iPk).  */
  pPk = sqlite4FindPrimaryKey(pTab, &iPk);
  assert( (pPk==0)==IsView(pTab) );
  bImplicitPK = (pPk && pPk->aiColumn[0]==-1);

  /* Figure out if we have any triggers and if the table being
  ** inserted into is a view. */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);
  isView = pTab->pSelect!=0;
#else
................................................................................
  int baseCur
){
  int i;
  Index *pIdx;
  Vdbe *v;

  assert( pTab->pIndex==0 || IsVirtual(pTab)==0 );

  assert( pTab->pIndex==0 || IsView(pTab)==0 );


  v = sqlite4GetVdbe(pParse);
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    sqlite4VdbeAddOp1(v, OP_Close, baseCur+i);
  }
}

Changes to src/pragma.c.

582
583
584
585
586
587
588



589
590
591
592
593
594
595
        Table *pTab = (Table *)sqliteHashData(x);
        int addrRewind;
        int nIdx = 0;
        int iPkCsr;
        Index *pPk;
        int iCsr;




        /* Open all indexes for table pTab. */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->eIndexType==SQLITE_INDEX_PRIMARYKEY ){
            pPk = pIdx;
            iPkCsr = nIdx+baseCsr;
          }
          nIdx++;







>
>
>







582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
        Table *pTab = (Table *)sqliteHashData(x);
        int addrRewind;
        int nIdx = 0;
        int iPkCsr;
        Index *pPk;
        int iCsr;

        /* Do nothing for views */
        if( IsView(pTab) ) continue;

        /* Open all indexes for table pTab. */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->eIndexType==SQLITE_INDEX_PRIMARYKEY ){
            pPk = pIdx;
            iPkCsr = nIdx+baseCsr;
          }
          nIdx++;

Changes to src/sqliteInt.h.

1342
1343
1344
1345
1346
1347
1348







1349
1350
1351
1352
1353
1354
1355
....
2890
2891
2892
2893
2894
2895
2896


2897
2898
2899
2900
2901
2902
2903
#  define IsVirtual(X)      (((X)->tabFlags & TF_Virtual)!=0)
#  define IsHiddenColumn(X) ((X)->isHidden)
#else
#  define IsVirtual(X)      0
#  define IsHiddenColumn(X) 0
#endif








/*
** Each foreign key constraint is an instance of the following structure.
**
** A foreign key is associated with two tables.  The "from" table is
** the table that contains the REFERENCES clause that creates the foreign
** key.  The "to" table is the table that is named in the REFERENCES clause.
** Consider this example:
................................................................................
void sqlite4RegisterGlobalFunctions(void);
int sqlite4SafetyCheckOk(sqlite4*);
int sqlite4SafetyCheckSickOrOk(sqlite4*);
void sqlite4ChangeCookie(Parse*, int);

#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
void sqlite4MaterializeView(Parse*, Table*, Expr*, int);


#endif

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite4BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite4FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite4DropTrigger(Parse*, SrcList*, int);







>
>
>
>
>
>
>







 







>
>







1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
....
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
#  define IsVirtual(X)      (((X)->tabFlags & TF_Virtual)!=0)
#  define IsHiddenColumn(X) ((X)->isHidden)
#else
#  define IsVirtual(X)      0
#  define IsHiddenColumn(X) 0
#endif

/* Test to see if a table is actually a view. */
#ifndef SQLITE_OMIT_VIEW
#  define IsView(X)         ((X)->pSelect!=0)
#else
#  define IsView(X)         0
#endif

/*
** Each foreign key constraint is an instance of the following structure.
**
** A foreign key is associated with two tables.  The "from" table is
** the table that contains the REFERENCES clause that creates the foreign
** key.  The "to" table is the table that is named in the REFERENCES clause.
** Consider this example:
................................................................................
void sqlite4RegisterGlobalFunctions(void);
int sqlite4SafetyCheckOk(sqlite4*);
int sqlite4SafetyCheckSickOrOk(sqlite4*);
void sqlite4ChangeCookie(Parse*, int);

#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
void sqlite4MaterializeView(Parse*, Table*, Expr*, int);
#else
# define sqlite4MaterializeView(w,x,y,z)
#endif

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite4BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite4FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite4DropTrigger(Parse*, SrcList*, int);

Changes to src/update.c.

123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
148
149
150
151
152
153
154

155
156

157
158
159
160
161
162
163
...
180
181
182
183
184
185
186

187
188
189
190
191
192
193
194
195
196
197
...
219
220
221
222
223
224
225

226
227

228
229
230
231
232
233
234
  int regOldKey;                  /* Register containing the original PK */

  int regNewRowid;       /* The new rowid */
  int regNew;            /* Content of the NEW.* table in triggers */

  int regOld = 0;                 /* Content of OLD.* table in triggers */
  int regKeySet = 0;              /* Register containing KeySet object */
  Index *pPk;                     /* The primary key index of this table */
  int iPk;                        /* Offset of primary key in aRegIdx[] */
  int bChngPk = 0;                /* True if any PK columns are updated */
  int bOpenAll = 0;               /* True if all indexes were opened */
  int bImplicitPk;                /* True if pTab has an implicit PK */
  int regOldTr = 0;               /* Content of OLD.* table including IPK */
  int regNewTr = 0;               /* Content of NEW.* table including IPK */

  memset(&sContext, 0, sizeof(sContext));
  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto update_cleanup;
................................................................................
  **   iDb
  **   pPk
  **   bImplicitPk
  */
  pTab = sqlite4SrcListLookup(pParse, pSrc);
  if( pTab==0 ) goto update_cleanup;
  iDb = sqlite4SchemaToIndex(pParse->db, pTab->pSchema);

  pPk = sqlite4FindPrimaryKey(pTab, &iPk);
  bImplicitPk = (pPk->aiColumn[0]<0);


  /* Figure out if we have any triggers and if the table being
  ** updated is a view.
  */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask);
  isView = pTab->pSelect!=0;
................................................................................
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* Allocate a cursors for the main database table and for all indices.
  ** The index cursors might not be used, but if they are used they
  ** need to occur right after the database cursor.  So go ahead and
  ** allocate enough space, just in case.  */
  iCur = pParse->nTab;

  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }
  pSrc->a[0].iCursor = iCur+iPk;

  /* Initialize the name-context */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pSrc;

  /* Resolve the column names in all the expressions of the of the UPDATE 
................................................................................
      sqlite4ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
      pParse->checkSchema = 1;
      goto update_cleanup;
    }
    aXRef[j] = i;

    /* Check if this column is part of the primary key. If so, set bChngPk. */

    for(iPkCol=0; iPkCol<pPk->nColumn; iPkCol++){
      if( pPk->aiColumn[iPkCol]==j ) bChngPk = 1;

    }

#ifndef SQLITE_OMIT_AUTHORIZATION
    {
      int rc;
      rc = sqlite4AuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
                           pTab->aCol[j].zName, db->aDb[iDb].zName);







|
|


|







 







>
|
|
>







 







>



|







 







>
|
|
>







123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
...
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
...
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
  int regOldKey;                  /* Register containing the original PK */

  int regNewRowid;       /* The new rowid */
  int regNew;            /* Content of the NEW.* table in triggers */

  int regOld = 0;                 /* Content of OLD.* table in triggers */
  int regKeySet = 0;              /* Register containing KeySet object */
  Index *pPk = 0;                 /* The primary key index of this table */
  int iPk = 0;                    /* Offset of primary key in aRegIdx[] */
  int bChngPk = 0;                /* True if any PK columns are updated */
  int bOpenAll = 0;               /* True if all indexes were opened */
  int bImplicitPk = 0;            /* True if pTab has an implicit PK */
  int regOldTr = 0;               /* Content of OLD.* table including IPK */
  int regNewTr = 0;               /* Content of NEW.* table including IPK */

  memset(&sContext, 0, sizeof(sContext));
  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto update_cleanup;
................................................................................
  **   iDb
  **   pPk
  **   bImplicitPk
  */
  pTab = sqlite4SrcListLookup(pParse, pSrc);
  if( pTab==0 ) goto update_cleanup;
  iDb = sqlite4SchemaToIndex(pParse->db, pTab->pSchema);
  if( IsView(pTab)==0 ){
    pPk = sqlite4FindPrimaryKey(pTab, &iPk);
    bImplicitPk = (pPk->aiColumn[0]<0);
  }

  /* Figure out if we have any triggers and if the table being
  ** updated is a view.
  */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask);
  isView = pTab->pSelect!=0;
................................................................................
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* Allocate a cursors for the main database table and for all indices.
  ** The index cursors might not be used, but if they are used they
  ** need to occur right after the database cursor.  So go ahead and
  ** allocate enough space, just in case.  */
  iCur = pParse->nTab;
  pSrc->a[0].iCursor = iCur+iPk;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }
  if( IsView(pTab) ) pParse->nTab++;

  /* Initialize the name-context */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pSrc;

  /* Resolve the column names in all the expressions of the of the UPDATE 
................................................................................
      sqlite4ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
      pParse->checkSchema = 1;
      goto update_cleanup;
    }
    aXRef[j] = i;

    /* Check if this column is part of the primary key. If so, set bChngPk. */
    if( !IsView(pTab) ){
      for(iPkCol=0; iPkCol<pPk->nColumn; iPkCol++){
        if( pPk->aiColumn[iPkCol]==j ) bChngPk = 1;
      }
    }

#ifndef SQLITE_OMIT_AUTHORIZATION
    {
      int rc;
      rc = sqlite4AuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
                           pTab->aCol[j].zName, db->aDb[iDb].zName);

Changes to test/simple.test.

671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687




















688
689
690
691
692
693
694
#-------------------------------------------------------------------------
reset_db
do_execsql_test 37.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
  INSERT INTO t1 VALUES('x', 'xxx');
  INSERT INTO t1 VALUES('y', 'yyy');
}

do_execsql_test 37.2 {
  BEGIN;
    DELETE FROM t1 WHERE a='y';
    INSERT INTO t1 VALUES('y', 'yyy');
    DELETE FROM t1 WHERE a='y';
    INSERT INTO t1 VALUES('y', 'yyy');
  ROLLBACK;
}
 





















#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');







<









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







671
672
673
674
675
676
677

678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
#-------------------------------------------------------------------------
reset_db
do_execsql_test 37.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
  INSERT INTO t1 VALUES('x', 'xxx');
  INSERT INTO t1 VALUES('y', 'yyy');
}

do_execsql_test 37.2 {
  BEGIN;
    DELETE FROM t1 WHERE a='y';
    INSERT INTO t1 VALUES('y', 'yyy');
    DELETE FROM t1 WHERE a='y';
    INSERT INTO t1 VALUES('y', 'yyy');
  ROLLBACK;
}
 
#-------------------------------------------------------------------------
reset_db
do_execsql_test 38.1 {
  CREATE TABLE t1(a, b);
  CREATE TABLE log(a, b);

  -- INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);

  CREATE VIEW v1 AS SELECT a, b FROM t1;
  CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN
    INSERT INTO log VALUES(old.b, old.a);
  END;
}

do_execsql_test 38.2 {
  DELETE FROM v1 WHERE a = 3;
  SELECT * FROM log;
} {4 3}


#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');

Changes to test/trigger2.test.

596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639




640
641
642




643


644
645
646
647
648
649

650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
  execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);

  CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 
      olda, oldb, oldc, oldd, newa, newb, newc, newd);

  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;

  CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;
  CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;

  CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;
  CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;

  CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
  END;
   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {};

do_test trigger2-7.2 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;




    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;




  }


} [ list 1 1 2 3 4 100 25 3 4 \
         2 1 2 3 4 100 25 3 4 \
	 3 1 2 3 4 0 0 0 0 \
	 4 1 2 3 4 0 0 0 0 \
	 5 0 0 0 0 10 20 30 40 \
	 6 0 0 0 0 10 20 30 40 ]


do_test trigger2-7.3 {
  execsql {
    DELETE FROM tlog;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    SELECT * FROM tlog;
  }
} [ list \
   1 0 0 0 0 10 20 30 40 \
   2 0 0 0 0 10 20 30 40 \
   3 1 2 3 4 100 25 3 4 \
   4 1 2 3 4 100 25 3 4 \
   5 1 2 3 4 0 0 0 0 \
   6 1 2 3 4 0 0 0 0 \
]
do_test trigger2-7.4 {
  execsql {
    DELETE FROM tlog;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    SELECT * FROM tlog;
  }
} [ list \
   1 1 2 3 4 0 0 0 0 \
   2 1 2 3 4 0 0 0 0 \
   3 0 0 0 0 10 20 30 40 \
   4 0 0 0 0 10 20 30 40 \
   5 1 2 3 4 100 25 3 4 \
   6 1 2 3 4 100 25 3 4 \
]

do_test trigger2-8.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
    CREATE VIEW v1 AS







<
|




|



|




|



|




|



|





<
<
|
>
>
>
>
|
<
|
>
>
>
>
|
>
>
|
|
|
|
|
|
>










|
|
|
|
|
|










|
|
|
|
|
|







596
597
598
599
600
601
602

603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635


636
637
638
639
640
641

642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
  execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);


  CREATE TABLE tlog(olda, oldb, oldc, oldd, newa, newb, newc, newd);

  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;

  CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;
  CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;

  CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;
  CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;

  CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
  END;
   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {};



do_execsql_test trigger2-7.2.1 { UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1 }
do_execsql_test trigger2.7.2.2 { SELECT * FROM tlog } {
  1 2 3 4 100 25 3 4 
  1 2 3 4 100 25 3 4 
}
do_execsql_test trigger2-7.2.3 { DELETE FROM abcd WHERE a = 1 }

do_execsql_test trigger2.7.2.4 { SELECT * FROM tlog } {
  1 2 3 4 100 25 3 4 
  1 2 3 4 100 25 3 4 
  1 2 3 4 0 0 0 0 
  1 2 3 4 0 0 0 0
}
do_execsql_test trigger2-7.2.5 { INSERT INTO abcd VALUES(10, 20, 30, 40) }
do_execsql_test trigger2.7.2.6 { SELECT * FROM tlog } {
  1 2 3 4 100 25 3 4 
  1 2 3 4 100 25 3 4 
  1 2 3 4 0 0 0 0 
  1 2 3 4 0 0 0 0
  0 0 0 0 10 20 30 40 
  0 0 0 0 10 20 30 40 
}

do_test trigger2-7.3 {
  execsql {
    DELETE FROM tlog;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    SELECT * FROM tlog;
  }
} [ list \
   0 0 0 0 10 20 30 40 \
   0 0 0 0 10 20 30 40 \
   1 2 3 4 100 25 3 4 \
   1 2 3 4 100 25 3 4 \
   1 2 3 4 0 0 0 0 \
   1 2 3 4 0 0 0 0 \
]
do_test trigger2-7.4 {
  execsql {
    DELETE FROM tlog;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    SELECT * FROM tlog;
  }
} [ list \
   1 2 3 4 0 0 0 0 \
   1 2 3 4 0 0 0 0 \
   0 0 0 0 10 20 30 40 \
   0 0 0 0 10 20 30 40 \
   1 2 3 4 100 25 3 4 \
   1 2 3 4 100 25 3 4 \
]

do_test trigger2-8.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
    CREATE VIEW v1 AS