Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Move [7d30880114] to the trunk. Add optimizations to reduce the number of opcodes used for BEFORE UPDATE triggers. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1b7c5250ccb63182324bfc3f1ea28f17 |
User & Date: | dan 2009-11-27 12:12:34.000 |
Context
2009-11-27
| ||
12:14 | Remove a C++ism accidentally added to fts3.c. (check-in: 97d3324160 user: dan tags: trunk) | |
12:12 | Move [7d30880114] to the trunk. Add optimizations to reduce the number of opcodes used for BEFORE UPDATE triggers. (check-in: 1b7c5250cc user: dan tags: trunk) | |
2009-11-26
| ||
14:01 | Simplifications to the sqlite3_trace() bound parameter substitution logic. (check-in: cb4b928648 user: drh tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
492 493 494 495 496 497 498 | ** use for the old.* references in the triggers. */ if( sqlite3FkRequired(pParse, pTab, 0, 0) || pTrigger ){ u32 mask; /* Mask of OLD.* columns in use */ int iCol; /* Iterator used while populating OLD.* */ /* TODO: Could use temporary registers here. Also could attempt to ** avoid copying the contents of the rowid register. */ | | > > | 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 | ** use for the old.* references in the triggers. */ if( sqlite3FkRequired(pParse, pTab, 0, 0) || pTrigger ){ u32 mask; /* Mask of OLD.* columns in use */ int iCol; /* Iterator used while populating OLD.* */ /* TODO: Could use temporary registers here. Also could attempt to ** avoid copying the contents of the rowid register. */ mask = sqlite3TriggerColmask( pParse, pTrigger, 0, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onconf ); mask |= sqlite3FkOldmask(pParse, pTab); iOld = pParse->nMem+1; pParse->nMem += (1 + pTab->nCol); /* Populate the OLD.* pseudo-table register array. These values will be ** used by any BEFORE and AFTER triggers that exist. */ sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld); |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
260 261 262 263 264 265 266 267 268 269 270 271 272 273 | cnt++; if( iCol<0 ){ pExpr->affinity = SQLITE_AFF_INTEGER; }else if( pExpr->iTable==0 ){ testcase( iCol==31 ); testcase( iCol==32 ); pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); } pExpr->iColumn = (i16)iCol; pExpr->pTab = pTab; isTrigger = 1; } } } | > > > > | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 | cnt++; if( iCol<0 ){ pExpr->affinity = SQLITE_AFF_INTEGER; }else if( pExpr->iTable==0 ){ testcase( iCol==31 ); testcase( iCol==32 ); pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); }else{ testcase( iCol==31 ); testcase( iCol==32 ); pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); } pExpr->iColumn = (i16)iCol; pExpr->pTab = pTab; isTrigger = 1; } } } |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2055 2056 2057 2058 2059 2060 2061 | ** ** A Vdbe sub-program that implements the body and WHEN clause of trigger ** TriggerPrg.pTrigger, assuming a default ON CONFLICT clause of ** TriggerPrg.orconf, is stored in the TriggerPrg.pProgram variable. ** The Parse.pTriggerPrg list never contains two entries with the same ** values for both pTrigger and orconf. ** | | | > | | 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 | ** ** A Vdbe sub-program that implements the body and WHEN clause of trigger ** TriggerPrg.pTrigger, assuming a default ON CONFLICT clause of ** TriggerPrg.orconf, is stored in the TriggerPrg.pProgram variable. ** The Parse.pTriggerPrg list never contains two entries with the same ** values for both pTrigger and orconf. ** ** The TriggerPrg.aColmask[0] variable is set to a mask of old.* columns ** accessed (or set to 0 for triggers fired as a result of INSERT ** statements). Similarly, the TriggerPrg.aColmask[1] variable is set to ** a mask of new.* columns used by the program. */ struct TriggerPrg { Trigger *pTrigger; /* Trigger this program was coded from */ int orconf; /* Default ON CONFLICT policy */ SubProgram *pProgram; /* Program implementing pTrigger/orconf */ u32 aColmask[2]; /* Masks of old.*, new.* columns accessed */ TriggerPrg *pNext; /* Next entry in Parse.pTriggerPrg list */ }; /* ** An SQL parser context. A copy of this structure is passed through ** the parser and down into all the parser action routine in order to ** carry around information that is global to the entire parse. |
︙ | ︙ | |||
2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 | AutoincInfo *pAinc; /* Information about AUTOINCREMENT counters */ int nMaxArg; /* Max args passed to user function by sub-program */ /* Information used while coding trigger programs. */ Parse *pToplevel; /* Parse structure for main program (or NULL) */ Table *pTriggerTab; /* Table triggers are being coded for */ u32 oldmask; /* Mask of old.* columns referenced */ u8 eTriggerOp; /* TK_UPDATE, TK_INSERT or TK_DELETE */ u8 eOrconf; /* Default ON CONFLICT policy for trigger steps */ u8 disableTriggers; /* True to disable triggers */ /* Above is constant between recursions. Below is reset before and after ** each recursion */ | > | 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 | AutoincInfo *pAinc; /* Information about AUTOINCREMENT counters */ int nMaxArg; /* Max args passed to user function by sub-program */ /* Information used while coding trigger programs. */ Parse *pToplevel; /* Parse structure for main program (or NULL) */ Table *pTriggerTab; /* Table triggers are being coded for */ u32 oldmask; /* Mask of old.* columns referenced */ u32 newmask; /* Mask of new.* columns referenced */ u8 eTriggerOp; /* TK_UPDATE, TK_INSERT or TK_DELETE */ u8 eOrconf; /* Default ON CONFLICT policy for trigger steps */ u8 disableTriggers; /* True to disable triggers */ /* Above is constant between recursions. Below is reset before and after ** each recursion */ |
︙ | ︙ | |||
2732 2733 2734 2735 2736 2737 2738 | TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, ExprList*,Select*,u8); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); | | | | 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 | TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, ExprList*,Select*,u8); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); u32 sqlite3TriggerColmask(Parse*,Trigger*,ExprList*,int,int,Table*,int); # define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p)) #else # define sqlite3TriggersExist(B,C,D,E,F) 0 # define sqlite3DeleteTrigger(A,B) # define sqlite3DropTriggerPtr(A,B) # define sqlite3UnlinkAndDeleteTrigger(A,B,C) # define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I) # define sqlite3CodeRowTriggerDirect(A,B,C,D,E,F) # define sqlite3TriggerList(X, Y) 0 # define sqlite3ParseToplevel(p) p # define sqlite3TriggerColmask(A,B,C,D,E,F,G) 0 #endif int sqlite3JoinType(Parse*, Token*, Token*, Token*); void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); #ifndef SQLITE_OMIT_AUTHORIZATION void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*); |
︙ | ︙ |
Changes to src/trigger.c.
︙ | ︙ | |||
807 808 809 810 811 812 813 | pPrg->pNext = pTop->pTriggerPrg; pTop->pTriggerPrg = pPrg; pPrg->pProgram = pProgram = sqlite3DbMallocZero(db, sizeof(SubProgram)); if( !pProgram ) return 0; pProgram->nRef = 1; pPrg->pTrigger = pTrigger; pPrg->orconf = orconf; | | > | 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 | pPrg->pNext = pTop->pTriggerPrg; pTop->pTriggerPrg = pPrg; pPrg->pProgram = pProgram = sqlite3DbMallocZero(db, sizeof(SubProgram)); if( !pProgram ) return 0; pProgram->nRef = 1; pPrg->pTrigger = pTrigger; pPrg->orconf = orconf; pPrg->aColmask[0] = 0xffffffff; pPrg->aColmask[1] = 0xffffffff; /* Allocate and populate a new Parse context to use for coding the ** trigger sub-program. */ pSubParse = sqlite3StackAllocZero(db, sizeof(Parse)); if( !pSubParse ) return 0; memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pSubParse; |
︙ | ︙ | |||
868 869 870 871 872 873 874 | transferParseError(pParse, pSubParse); if( db->mallocFailed==0 ){ pProgram->aOp = sqlite3VdbeTakeOpArray(v, &pProgram->nOp, &pTop->nMaxArg); } pProgram->nMem = pSubParse->nMem; pProgram->nCsr = pSubParse->nTab; pProgram->token = (void *)pTrigger; | | > | 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 | transferParseError(pParse, pSubParse); if( db->mallocFailed==0 ){ pProgram->aOp = sqlite3VdbeTakeOpArray(v, &pProgram->nOp, &pTop->nMaxArg); } pProgram->nMem = pSubParse->nMem; pProgram->nCsr = pSubParse->nTab; pProgram->token = (void *)pTrigger; pPrg->aColmask[0] = pSubParse->oldmask; pPrg->aColmask[1] = pSubParse->newmask; sqlite3VdbeDelete(v); } assert( !pSubParse->pAinc && !pSubParse->pZombieTab ); assert( !pSubParse->pTriggerPrg && !pSubParse->nMaxArg ); sqlite3StackFree(db, pSubParse); |
︙ | ︙ | |||
1028 1029 1030 1031 1032 1033 1034 | ){ sqlite3CodeRowTriggerDirect(pParse, p, pTab, reg, orconf, ignoreJump); } } } /* | | | | | | < | | | > | > > > > > > | > > > > | > | | 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 | ){ sqlite3CodeRowTriggerDirect(pParse, p, pTab, reg, orconf, ignoreJump); } } } /* ** Triggers may access values stored in the old.* or new.* pseudo-table. ** This function returns a 32-bit bitmask indicating which columns of the ** old.* or new.* tables actually are used by triggers. This information ** may be used by the caller, for example, to avoid having to load the entire ** old.* record into memory when executing an UPDATE or DELETE command. ** ** Bit 0 of the returned mask is set if the left-most column of the ** table may be accessed using an [old|new].<col> reference. Bit 1 is set if ** the second leftmost column value is required, and so on. If there ** are more than 32 columns in the table, and at least one of the columns ** with an index greater than 32 may be accessed, 0xffffffff is returned. ** ** It is not possible to determine if the old.rowid or new.rowid column is ** accessed by triggers. The caller must always assume that it is. ** ** Parameter isNew must be either 1 or 0. If it is 0, then the mask returned ** applies to the old.* table. If 1, the new.* table. ** ** Parameter tr_tm must be a mask with one or both of the TRIGGER_BEFORE ** and TRIGGER_AFTER bits set. Values accessed by BEFORE triggers are only ** included in the returned mask if the TRIGGER_BEFORE bit is set in the ** tr_tm parameter. Similarly, values accessed by AFTER triggers are only ** included in the returned mask if the TRIGGER_AFTER bit is set in tr_tm. */ u32 sqlite3TriggerColmask( Parse *pParse, /* Parse context */ Trigger *pTrigger, /* List of triggers on table pTab */ ExprList *pChanges, /* Changes list for any UPDATE OF triggers */ int isNew, /* 1 for new.* ref mask, 0 for old.* ref mask */ int tr_tm, /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */ Table *pTab, /* The table to code triggers from */ int orconf /* Default ON CONFLICT policy for trigger steps */ ){ const int op = pChanges ? TK_UPDATE : TK_DELETE; u32 mask = 0; Trigger *p; assert( isNew==1 || isNew==0 ); for(p=pTrigger; p; p=p->pNext){ if( p->op==op && (tr_tm&p->tr_tm) && checkColumnOverlap(p->pColumns,pChanges) ){ TriggerPrg *pPrg; pPrg = getRowTrigger(pParse, p, pTab, orconf); if( pPrg ){ mask |= pPrg->aColmask[isNew]; } } } return mask; } #endif /* !defined(SQLITE_OMIT_TRIGGER) */ |
Changes to src/update.c.
︙ | ︙ | |||
107 108 109 110 111 112 113 | ** aXRef[i]==-1 if the i-th column is not changed. */ int chngRowid; /* True if the record number is being changed */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ | < | | > > | 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | ** aXRef[i]==-1 if the i-th column is not changed. */ int chngRowid; /* True if the record number is being changed */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int okOnePass; /* True for one-pass algorithm without the FIFO */ int hasFK; /* True if foreign key processing is required */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True when updating a view (INSTEAD OF trigger) */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */ #endif int newmask; /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid; /* The old rowid */ int regNewRowid; /* The new rowid */ int regNew; int regOld = 0; |
︙ | ︙ | |||
142 143 144 145 146 147 148 | if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); /* Figure out if we have any triggers and if the table being ** updated is a view. */ #ifndef SQLITE_OMIT_TRIGGER | | > > | | 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); /* Figure out if we have any triggers and if the table being ** updated is a view. */ #ifndef SQLITE_OMIT_TRIGGER pTrigger = sqlite3TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask); isView = pTab->pSelect!=0; assert( pTrigger || tmask==0 ); #else # define pTrigger 0 # define isView 0 # define tmask 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ goto update_cleanup; } aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol ); if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* Allocate a cursors for the main database table and for all indices. |
︙ | ︙ | |||
384 385 386 387 388 389 390 | sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } /* If there are triggers on this table, populate an array of registers ** with the required old.* column data. */ if( hasFK || pTrigger ){ u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); | | > > | > > > > > > > > > > > > | > > > > > > > < < | > | | > > > > > > > > > > > > > > | 387 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 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 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 | sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } /* If there are triggers on this table, populate an array of registers ** with the required old.* column data. */ if( hasFK || pTrigger ){ u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); oldmask |= sqlite3TriggerColmask(pParse, pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i); sqlite3ColumnDefault(v, pTab, i, regOld+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); } } if( chngRowid==0 ){ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid); } } /* Populate the array of registers beginning at regNew with the new ** row data. This array is used to check constaints, create the new ** table and index records, and as the values for any new.* references ** made by triggers. ** ** If there are one or more BEFORE triggers, then do not populate the ** registers associated with columns that are (a) not modified by ** this UPDATE statement and (b) not accessed by new.* references. The ** values for registers not modified by the UPDATE must be reloaded from ** the database after the BEFORE triggers are fired anyway (as the trigger ** may have modified them). So not loading those that are not going to ** be used eliminates some redundant opcodes. */ newmask = sqlite3TriggerColmask( pParse, pTrigger, pChanges, 1, TRIGGER_BEFORE, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){ /* This branch loads the value of a column that will not be changed ** into a register. This is done if there are no BEFORE triggers, or ** if there are one or more BEFORE triggers that use this value via ** a new.* reference in a trigger program. */ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regNew+i); sqlite3ColumnDefault(v, pTab, i, regNew+i); } } } /* Fire any BEFORE UPDATE triggers. This happens before constraints are ** verified. One could argue that this is wrong. */ if( tmask&TRIGGER_BEFORE ){ sqlite3VdbeAddOp2(v, OP_Affinity, regNew, pTab->nCol); sqlite3TableAffinityStr(v, pTab); sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, regOldRowid, onError, addr); /* The row-trigger may have deleted the row being updated. In this ** case, jump to the next row. No updates or AFTER triggers are ** required. This behaviour - what happens when the row being updated ** is deleted or renamed by a BEFORE trigger - is left undefined in the ** documentation. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* If it did not delete it, the row-trigger may still have modified ** some of the columns of the row being updated. Load the values for ** all columns not modified by the update statement into their ** registers in case this has happened. */ for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 && i!=pTab->iPKey ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regNew+i); sqlite3ColumnDefault(v, pTab, i, regNew+i); } } } if( !isView ){ int j1; /* Address of jump instruction */ /* Do constraint checks. */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Do FK constraint checks. */ if( hasFK ){ |
︙ | ︙ |
Changes to test/triggerC.test.
︙ | ︙ | |||
780 781 782 783 784 785 786 | CREATE TRIGGER t9r1 AFTER DELETE ON t9 BEGIN DELETE FROM t9 WHERE b=old.a; END; DELETE FROM t9 WHERE b=4; SELECT a FROM t9 ORDER BY a; } } {1 2 3 4} | | > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 | CREATE TRIGGER t9r1 AFTER DELETE ON t9 BEGIN DELETE FROM t9 WHERE b=old.a; END; DELETE FROM t9 WHERE b=4; SELECT a FROM t9 ORDER BY a; } } {1 2 3 4} # At one point (between versions 3.6.18 and 3.6.20 inclusive), an UPDATE # that fired a BEFORE trigger that itself updated the same row as the # statement causing it to fire was causing a strange side-effect: The # values updated by the statement within the trigger were being overwritten # by the values in the new.* array, even if those values were not # themselves written by the parent UPDATE statement. # # Technically speaking this was not a bug. The SQLite documentation says # that if a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes the # row that the parent statement is operating on the results are undefined. # But as of 3.6.21 behaviour is restored to the way it was in versions # 3.6.17 and earlier to avoid causing unnecessary difficulties. # do_test triggerC-10.1 { execsql { CREATE TABLE t10(a, updatecnt DEFAULT 0); CREATE TRIGGER t10_bu BEFORE UPDATE OF a ON t10 BEGIN UPDATE t10 SET updatecnt = updatecnt+1 WHERE rowid = old.rowid; END; INSERT INTO t10(a) VALUES('hello'); } # Before the problem was fixed, table t10 would contain the tuple # (world, 0) after running the following script (because the value # 1 written to column "updatecnt" was clobbered by the old value 0). # execsql { UPDATE t10 SET a = 'world'; SELECT * FROM t10; } } {world 1} do_test triggerC-10.2 { execsql { UPDATE t10 SET a = 'tcl', updatecnt = 5; SELECT * FROM t10; } } {tcl 5} do_test triggerC-10.3 { execsql { CREATE TABLE t11( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40 ); CREATE TRIGGER t11_bu BEFORE UPDATE OF c1 ON t11 BEGIN UPDATE t11 SET c31 = c31+1, c32=c32+1 WHERE rowid = old.rowid; END; INSERT INTO t11 VALUES( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 ); } # Before the problem was fixed, table t10 would contain the tuple # (world, 0) after running the following script (because the value # 1 written to column "updatecnt" was clobbered by the old value 0). # execsql { UPDATE t11 SET c4=35, c33=22, c1=5; SELECT * FROM t11; } } {5 2 3 35 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 32 33 22 34 35 36 37 38 39 40} finish_test |