AryaWu/sqlite
0
1/*2**3** The author disclaims copyright to this source code. In place of4** a legal notice, here is a blessing:5**6** May you do good and not evil.7** May you find forgiveness for yourself and forgive others.8** May you share freely, never taking more than you give.9**10*************************************************************************11** This file contains the implementation for TRIGGERs12*/13#include "sqliteInt.h"14 15#ifndef SQLITE_OMIT_TRIGGER16/*17** Delete a linked list of TriggerStep structures.18*/19void sqlite3DeleteTriggerStep(sqlite3 *db, TriggerStep *pTriggerStep){20 while( pTriggerStep ){21 TriggerStep * pTmp = pTriggerStep;22 pTriggerStep = pTriggerStep->pNext;23 24 sqlite3ExprDelete(db, pTmp->pWhere);25 sqlite3ExprListDelete(db, pTmp->pExprList);26 sqlite3SelectDelete(db, pTmp->pSelect);27 sqlite3IdListDelete(db, pTmp->pIdList);28 sqlite3UpsertDelete(db, pTmp->pUpsert);29 sqlite3SrcListDelete(db, pTmp->pSrc);30 sqlite3DbFree(db, pTmp->zSpan);31 32 sqlite3DbFree(db, pTmp);33 }34}35 36/*37** Given table pTab, return a list of all the triggers attached to 38** the table. The list is connected by Trigger.pNext pointers.39**40** All of the triggers on pTab that are in the same database as pTab41** are already attached to pTab->pTrigger. But there might be additional42** triggers on pTab in the TEMP schema. This routine prepends all43** TEMP triggers on pTab to the beginning of the pTab->pTrigger list44** and returns the combined list.45**46** To state it another way: This routine returns a list of all triggers47** that fire off of pTab. The list will include any TEMP triggers on48** pTab as well as the triggers lised in pTab->pTrigger.49*/50Trigger *sqlite3TriggerList(Parse *pParse, Table *pTab){51 Schema *pTmpSchema; /* Schema of the pTab table */52 Trigger *pList; /* List of triggers to return */53 HashElem *p; /* Loop variable for TEMP triggers */54 55 assert( pParse->disableTriggers==0 );56 pTmpSchema = pParse->db->aDb[1].pSchema;57 p = sqliteHashFirst(&pTmpSchema->trigHash);58 pList = pTab->pTrigger;59 while( p ){60 Trigger *pTrig = (Trigger *)sqliteHashData(p);61 if( pTrig->pTabSchema==pTab->pSchema62 && pTrig->table63 && 0==sqlite3StrICmp(pTrig->table, pTab->zName)64 && (pTrig->pTabSchema!=pTmpSchema || pTrig->bReturning)65 ){66 pTrig->pNext = pList;67 pList = pTrig;68 }else if( pTrig->op==TK_RETURNING ){69#ifndef SQLITE_OMIT_VIRTUALTABLE70 assert( pParse->db->pVtabCtx==0 );71#endif72 assert( pParse->bReturning );73 assert( !pParse->isCreate );74 assert( &(pParse->u1.d.pReturning->retTrig) == pTrig );75 pTrig->table = pTab->zName;76 pTrig->pTabSchema = pTab->pSchema;77 pTrig->pNext = pList;78 pList = pTrig;79 } 80 p = sqliteHashNext(p); 81 }82#if 083 if( pList ){84 Trigger *pX;85 printf("Triggers for %s:", pTab->zName);86 for(pX=pList; pX; pX=pX->pNext){87 printf(" %s", pX->zName);88 }89 printf("\n");90 fflush(stdout);91 }92#endif93 return pList; 94}95 96/*97** This is called by the parser when it sees a CREATE TRIGGER statement98** up to the point of the BEGIN before the trigger actions. A Trigger99** structure is generated based on the information available and stored100** in pParse->pNewTrigger. After the trigger actions have been parsed, the101** sqlite3FinishTrigger() function is called to complete the trigger102** construction process.103*/104void sqlite3BeginTrigger(105 Parse *pParse, /* The parse context of the CREATE TRIGGER statement */106 Token *pName1, /* The name of the trigger */107 Token *pName2, /* The name of the trigger */108 int tr_tm, /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD */109 int op, /* One of TK_INSERT, TK_UPDATE, TK_DELETE */110 IdList *pColumns, /* column list if this is an UPDATE OF trigger */111 SrcList *pTableName,/* The name of the table/view the trigger applies to */112 Expr *pWhen, /* WHEN clause */113 int isTemp, /* True if the TEMPORARY keyword is present */114 int noErr /* Suppress errors if the trigger already exists */115){116 Trigger *pTrigger = 0; /* The new trigger */117 Table *pTab; /* Table that the trigger fires off of */118 char *zName = 0; /* Name of the trigger */119 sqlite3 *db = pParse->db; /* The database connection */120 int iDb; /* The database to store the trigger in */121 Token *pName; /* The unqualified db name */122 DbFixer sFix; /* State vector for the DB fixer */123 124 assert( pName1!=0 ); /* pName1->z might be NULL, but not pName1 itself */125 assert( pName2!=0 );126 assert( op==TK_INSERT || op==TK_UPDATE || op==TK_DELETE );127 assert( op>0 && op<0xff );128 if( isTemp ){129 /* If TEMP was specified, then the trigger name may not be qualified. */130 if( pName2->n>0 ){131 sqlite3ErrorMsg(pParse, "temporary trigger may not have qualified name");132 goto trigger_cleanup;133 }134 iDb = 1;135 pName = pName1;136 }else{137 /* Figure out the db that the trigger will be created in */138 iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pName);139 if( iDb<0 ){140 goto trigger_cleanup;141 }142 }143 if( !pTableName || db->mallocFailed ){144 goto trigger_cleanup;145 }146 147 /* A long-standing parser bug is that this syntax was allowed:148 **149 ** CREATE TRIGGER attached.demo AFTER INSERT ON attached.tab ....150 ** ^^^^^^^^151 **152 ** To maintain backwards compatibility, ignore the database153 ** name on pTableName if we are reparsing out of the schema table154 */155 if( db->init.busy && iDb!=1 ){156 assert( pTableName->a[0].fg.fixedSchema==0 );157 assert( pTableName->a[0].fg.isSubquery==0 );158 sqlite3DbFree(db, pTableName->a[0].u4.zDatabase);159 pTableName->a[0].u4.zDatabase = 0;160 }161 162 /* If the trigger name was unqualified, and the table is a temp table,163 ** then set iDb to 1 to create the trigger in the temporary database.164 ** If sqlite3SrcListLookup() returns 0, indicating the table does not165 ** exist, the error is caught by the block below.166 */167 pTab = sqlite3SrcListLookup(pParse, pTableName);168 if( db->init.busy==0 && pName2->n==0 && pTab169 && pTab->pSchema==db->aDb[1].pSchema ){170 iDb = 1;171 }172 173 /* Ensure the table name matches database name and that the table exists */174 if( db->mallocFailed ) goto trigger_cleanup;175 assert( pTableName->nSrc==1 );176 sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName);177 if( sqlite3FixSrcList(&sFix, pTableName) ){178 goto trigger_cleanup;179 }180 pTab = sqlite3SrcListLookup(pParse, pTableName);181 if( !pTab ){182 /* The table does not exist. */183 goto trigger_orphan_error;184 }185 if( IsVirtual(pTab) ){186 sqlite3ErrorMsg(pParse, "cannot create triggers on virtual tables");187 goto trigger_orphan_error;188 }189 if( (pTab->tabFlags & TF_Shadow)!=0 && sqlite3ReadOnlyShadowTables(db) ){190 sqlite3ErrorMsg(pParse, "cannot create triggers on shadow tables");191 goto trigger_orphan_error;192 }193 194 /* Check that the trigger name is not reserved and that no trigger of the195 ** specified name exists */196 zName = sqlite3NameFromToken(db, pName);197 if( zName==0 ){198 assert( db->mallocFailed );199 goto trigger_cleanup;200 }201 if( sqlite3CheckObjectName(pParse, zName, "trigger", pTab->zName) ){202 goto trigger_cleanup;203 }204 assert( sqlite3SchemaMutexHeld(db, iDb, 0) );205 if( !IN_RENAME_OBJECT ){206 if( sqlite3HashFind(&(db->aDb[iDb].pSchema->trigHash),zName) ){207 if( !noErr ){208 sqlite3ErrorMsg(pParse, "trigger %T already exists", pName);209 }else{210 assert( !db->init.busy );211 sqlite3CodeVerifySchema(pParse, iDb);212 VVA_ONLY( pParse->ifNotExists = 1; )213 }214 goto trigger_cleanup;215 }216 }217 218 /* Do not create a trigger on a system table */219 if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){220 sqlite3ErrorMsg(pParse, "cannot create trigger on system table");221 goto trigger_cleanup;222 }223 224 /* INSTEAD of triggers are only for views and views only support INSTEAD225 ** of triggers.226 */227 if( IsView(pTab) && tr_tm!=TK_INSTEAD ){228 sqlite3ErrorMsg(pParse, "cannot create %s trigger on view: %S", 229 (tr_tm == TK_BEFORE)?"BEFORE":"AFTER", pTableName->a);230 goto trigger_orphan_error;231 }232 if( !IsView(pTab) && tr_tm==TK_INSTEAD ){233 sqlite3ErrorMsg(pParse, "cannot create INSTEAD OF"234 " trigger on table: %S", pTableName->a);235 goto trigger_orphan_error;236 }237 238#ifndef SQLITE_OMIT_AUTHORIZATION239 if( !IN_RENAME_OBJECT ){240 int iTabDb = sqlite3SchemaToIndex(db, pTab->pSchema);241 int code = SQLITE_CREATE_TRIGGER;242 const char *zDb = db->aDb[iTabDb].zDbSName;243 const char *zDbTrig = isTemp ? db->aDb[1].zDbSName : zDb;244 if( iTabDb==1 || isTemp ) code = SQLITE_CREATE_TEMP_TRIGGER;245 if( sqlite3AuthCheck(pParse, code, zName, pTab->zName, zDbTrig) ){246 goto trigger_cleanup;247 }248 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, SCHEMA_TABLE(iTabDb),0,zDb)){249 goto trigger_cleanup;250 }251 }252#endif253 254 /* INSTEAD OF triggers can only appear on views and BEFORE triggers255 ** cannot appear on views. So we might as well translate every256 ** INSTEAD OF trigger into a BEFORE trigger. It simplifies code257 ** elsewhere.258 */259 if (tr_tm == TK_INSTEAD){260 tr_tm = TK_BEFORE;261 }262 263 /* Build the Trigger object */264 pTrigger = (Trigger*)sqlite3DbMallocZero(db, sizeof(Trigger));265 if( pTrigger==0 ) goto trigger_cleanup;266 pTrigger->zName = zName;267 zName = 0;268 pTrigger->table = sqlite3DbStrDup(db, pTableName->a[0].zName);269 pTrigger->pSchema = db->aDb[iDb].pSchema;270 pTrigger->pTabSchema = pTab->pSchema;271 pTrigger->op = (u8)op;272 pTrigger->tr_tm = tr_tm==TK_BEFORE ? TRIGGER_BEFORE : TRIGGER_AFTER;273 if( IN_RENAME_OBJECT ){274 sqlite3RenameTokenRemap(pParse, pTrigger->table, pTableName->a[0].zName);275 pTrigger->pWhen = pWhen;276 pWhen = 0;277 }else{278 pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);279 }280 pTrigger->pColumns = pColumns;281 pColumns = 0;282 assert( pParse->pNewTrigger==0 );283 pParse->pNewTrigger = pTrigger;284 285trigger_cleanup:286 sqlite3DbFree(db, zName);287 sqlite3SrcListDelete(db, pTableName);288 sqlite3IdListDelete(db, pColumns);289 sqlite3ExprDelete(db, pWhen);290 if( !pParse->pNewTrigger ){291 sqlite3DeleteTrigger(db, pTrigger);292 }else{293 assert( pParse->pNewTrigger==pTrigger );294 }295 return;296 297trigger_orphan_error:298 if( db->init.iDb==1 ){299 /* Ticket #3810.300 ** Normally, whenever a table is dropped, all associated triggers are301 ** dropped too. But if a TEMP trigger is created on a non-TEMP table302 ** and the table is dropped by a different database connection, the303 ** trigger is not visible to the database connection that does the304 ** drop so the trigger cannot be dropped. This results in an305 ** "orphaned trigger" - a trigger whose associated table is missing.306 **307 ** 2020-11-05 see also https://sqlite.org/forum/forumpost/157dc791df308 */309 db->init.orphanTrigger = 1;310 }311 goto trigger_cleanup;312}313 314/*315** This routine is called after all of the trigger actions have been parsed316** in order to complete the process of building the trigger.317*/318void sqlite3FinishTrigger(319 Parse *pParse, /* Parser context */320 TriggerStep *pStepList, /* The triggered program */321 Token *pAll /* Token that describes the complete CREATE TRIGGER */322){323 Trigger *pTrig = pParse->pNewTrigger; /* Trigger being finished */324 char *zName; /* Name of trigger */325 sqlite3 *db = pParse->db; /* The database */326 DbFixer sFix; /* Fixer object */327 int iDb; /* Database containing the trigger */328 Token nameToken; /* Trigger name for error reporting */329 330 pParse->pNewTrigger = 0;331 if( NEVER(pParse->nErr) || !pTrig ) goto triggerfinish_cleanup;332 zName = pTrig->zName;333 iDb = sqlite3SchemaToIndex(pParse->db, pTrig->pSchema);334 pTrig->step_list = pStepList;335 while( pStepList ){336 pStepList->pTrig = pTrig;337 pStepList = pStepList->pNext;338 }339 sqlite3TokenInit(&nameToken, pTrig->zName);340 sqlite3FixInit(&sFix, pParse, iDb, "trigger", &nameToken);341 if( sqlite3FixTriggerStep(&sFix, pTrig->step_list) 342 || sqlite3FixExpr(&sFix, pTrig->pWhen) 343 ){344 goto triggerfinish_cleanup;345 }346 347#ifndef SQLITE_OMIT_ALTERTABLE348 if( IN_RENAME_OBJECT ){349 assert( !db->init.busy );350 pParse->pNewTrigger = pTrig;351 pTrig = 0;352 }else353#endif354 355 /* if we are not initializing,356 ** build the sqlite_schema entry357 */358 if( !db->init.busy ){359 Vdbe *v;360 char *z;361 362 /* If this is a new CREATE TABLE statement, and if shadow tables363 ** are read-only, and the trigger makes a change to a shadow table,364 ** then raise an error - do not allow the trigger to be created. */365 if( sqlite3ReadOnlyShadowTables(db) ){366 TriggerStep *pStep;367 for(pStep=pTrig->step_list; pStep; pStep=pStep->pNext){368 if( pStep->pSrc!=0369 && sqlite3ShadowTableName(db, pStep->pSrc->a[0].zName)370 ){371 sqlite3ErrorMsg(pParse, 372 "trigger \"%s\" may not write to shadow table \"%s\"",373 pTrig->zName, pStep->pSrc->a[0].zName);374 goto triggerfinish_cleanup;375 }376 }377 }378 379 /* Make an entry in the sqlite_schema table */380 v = sqlite3GetVdbe(pParse);381 if( v==0 ) goto triggerfinish_cleanup;382 sqlite3BeginWriteOperation(pParse, 0, iDb);383 z = sqlite3DbStrNDup(db, (char*)pAll->z, pAll->n);384 testcase( z==0 );385 sqlite3NestedParse(pParse,386 "INSERT INTO %Q." LEGACY_SCHEMA_TABLE387 " VALUES('trigger',%Q,%Q,0,'CREATE TRIGGER %q')",388 db->aDb[iDb].zDbSName, zName,389 pTrig->table, z);390 sqlite3DbFree(db, z);391 sqlite3ChangeCookie(pParse, iDb);392 sqlite3VdbeAddParseSchemaOp(v, iDb,393 sqlite3MPrintf(db, "type='trigger' AND name='%q'", zName), 0);394 }395 396 if( db->init.busy ){397 Trigger *pLink = pTrig;398 Hash *pHash = &db->aDb[iDb].pSchema->trigHash;399 assert( sqlite3SchemaMutexHeld(db, iDb, 0) );400 assert( pLink!=0 );401 pTrig = sqlite3HashInsert(pHash, zName, pTrig);402 if( pTrig ){403 sqlite3OomFault(db);404 }else if( pLink->pSchema==pLink->pTabSchema ){405 Table *pTab;406 pTab = sqlite3HashFind(&pLink->pTabSchema->tblHash, pLink->table);407 assert( pTab!=0 );408 pLink->pNext = pTab->pTrigger;409 pTab->pTrigger = pLink;410 }411 }412 413triggerfinish_cleanup:414 sqlite3DeleteTrigger(db, pTrig);415 assert( IN_RENAME_OBJECT || !pParse->pNewTrigger );416 sqlite3DeleteTriggerStep(db, pStepList);417}418 419/*420** Duplicate a range of text from an SQL statement, then convert all421** whitespace characters into ordinary space characters.422*/423static char *triggerSpanDup(sqlite3 *db, const char *zStart, const char *zEnd){424 char *z = sqlite3DbSpanDup(db, zStart, zEnd);425 int i;426 if( z ) for(i=0; z[i]; i++) if( sqlite3Isspace(z[i]) ) z[i] = ' ';427 return z;428} 429 430/*431** Turn a SELECT statement (that the pSelect parameter points to) into432** a trigger step. Return a pointer to a TriggerStep structure.433**434** The parser calls this routine when it finds a SELECT statement in435** body of a TRIGGER. 436*/437TriggerStep *sqlite3TriggerSelectStep(438 sqlite3 *db, /* Database connection */439 Select *pSelect, /* The SELECT statement */440 const char *zStart, /* Start of SQL text */441 const char *zEnd /* End of SQL text */442){443 TriggerStep *pTriggerStep = sqlite3DbMallocZero(db, sizeof(TriggerStep));444 if( pTriggerStep==0 ) {445 sqlite3SelectDelete(db, pSelect);446 return 0;447 }448 pTriggerStep->op = TK_SELECT;449 pTriggerStep->pSelect = pSelect;450 pTriggerStep->orconf = OE_Default;451 pTriggerStep->zSpan = triggerSpanDup(db, zStart, zEnd);452 return pTriggerStep;453}454 455/*456** Allocate space to hold a new trigger step. The allocated space457** holds both the TriggerStep object and the TriggerStep.target.z string.458**459** If an OOM error occurs, NULL is returned and db->mallocFailed is set.460*/461static TriggerStep *triggerStepAllocate(462 Parse *pParse, /* Parser context */463 u8 op, /* Trigger opcode */464 SrcList *pTabList, /* Target table */465 const char *zStart, /* Start of SQL text */466 const char *zEnd /* End of SQL text */467){468 Trigger *pNew = pParse->pNewTrigger;469 sqlite3 *db = pParse->db;470 TriggerStep *pTriggerStep = 0;471 472 if( pParse->nErr==0 ){473 if( pNew 474 && pNew->pSchema!=db->aDb[1].pSchema 475 && pTabList->a[0].u4.zDatabase 476 ){477 sqlite3ErrorMsg(pParse, 478 "qualified table names are not allowed on INSERT, UPDATE, and DELETE "479 "statements within triggers");480 }else{481 pTriggerStep = sqlite3DbMallocZero(db, sizeof(TriggerStep));482 if( pTriggerStep ){483 pTriggerStep->pSrc = sqlite3SrcListDup(db, pTabList, EXPRDUP_REDUCE);484 pTriggerStep->op = op;485 pTriggerStep->zSpan = triggerSpanDup(db, zStart, zEnd);486 if( pTriggerStep->pSrc && IN_RENAME_OBJECT ){487 sqlite3RenameTokenRemap(pParse, 488 pTriggerStep->pSrc->a[0].zName, 489 pTabList->a[0].zName490 );491 }492 }493 }494 }495 496 sqlite3SrcListDelete(db, pTabList);497 return pTriggerStep;498}499 500/*501** Build a trigger step out of an INSERT statement. Return a pointer502** to the new trigger step.503**504** The parser calls this routine when it sees an INSERT inside the505** body of a trigger.506*/507TriggerStep *sqlite3TriggerInsertStep(508 Parse *pParse, /* Parser */509 SrcList *pTabList, /* Table to INSERT into */510 IdList *pColumn, /* List of columns in pTableName to insert into */511 Select *pSelect, /* A SELECT statement that supplies values */512 u8 orconf, /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */513 Upsert *pUpsert, /* ON CONFLICT clauses for upsert */514 const char *zStart, /* Start of SQL text */515 const char *zEnd /* End of SQL text */516){517 sqlite3 *db = pParse->db;518 TriggerStep *pTriggerStep;519 520 assert(pSelect != 0 || db->mallocFailed);521 522 pTriggerStep = triggerStepAllocate(pParse, TK_INSERT, pTabList, zStart, zEnd);523 if( pTriggerStep ){524 if( IN_RENAME_OBJECT ){525 pTriggerStep->pSelect = pSelect;526 pSelect = 0;527 }else{528 pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);529 }530 pTriggerStep->pIdList = pColumn;531 pTriggerStep->pUpsert = pUpsert;532 pTriggerStep->orconf = orconf;533 if( pUpsert ){534 sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget);535 }536 }else{537 testcase( pColumn );538 sqlite3IdListDelete(db, pColumn);539 testcase( pUpsert );540 sqlite3UpsertDelete(db, pUpsert);541 }542 sqlite3SelectDelete(db, pSelect);543 544 return pTriggerStep;545}546 547/*548** Construct a trigger step that implements an UPDATE statement and return549** a pointer to that trigger step. The parser calls this routine when it550** sees an UPDATE statement inside the body of a CREATE TRIGGER.551*/552TriggerStep *sqlite3TriggerUpdateStep(553 Parse *pParse, /* Parser */554 SrcList *pTabList, /* Name of the table to be updated */555 SrcList *pFrom, /* FROM clause for an UPDATE-FROM, or NULL */556 ExprList *pEList, /* The SET clause: list of column and new values */557 Expr *pWhere, /* The WHERE clause */558 u8 orconf, /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */559 const char *zStart, /* Start of SQL text */560 const char *zEnd /* End of SQL text */561){562 sqlite3 *db = pParse->db;563 TriggerStep *pTriggerStep;564 565 pTriggerStep = triggerStepAllocate(pParse, TK_UPDATE, pTabList, zStart, zEnd);566 if( pTriggerStep ){567 SrcList *pFromDup = 0;568 if( IN_RENAME_OBJECT ){569 pTriggerStep->pExprList = pEList;570 pTriggerStep->pWhere = pWhere;571 pFromDup = pFrom;572 pEList = 0;573 pWhere = 0;574 pFrom = 0;575 }else{576 pTriggerStep->pExprList = sqlite3ExprListDup(db, pEList, EXPRDUP_REDUCE);577 pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);578 pFromDup = sqlite3SrcListDup(db, pFrom, EXPRDUP_REDUCE);579 }580 pTriggerStep->orconf = orconf;581 582 if( pFromDup && !IN_RENAME_OBJECT){583 Select *pSub;584 Token as = {0, 0};585 pSub = sqlite3SelectNew(pParse, 0, pFromDup, 0,0,0,0, SF_NestedFrom, 0);586 pFromDup = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &as, pSub ,0);587 }588 if( pFromDup && pTriggerStep->pSrc ){589 pTriggerStep->pSrc = sqlite3SrcListAppendList(590 pParse, pTriggerStep->pSrc, pFromDup591 );592 }else{593 sqlite3SrcListDelete(db, pFromDup);594 }595 }596 sqlite3ExprListDelete(db, pEList);597 sqlite3ExprDelete(db, pWhere);598 sqlite3SrcListDelete(db, pFrom);599 return pTriggerStep;600}601 602/*603** Construct a trigger step that implements a DELETE statement and return604** a pointer to that trigger step. The parser calls this routine when it605** sees a DELETE statement inside the body of a CREATE TRIGGER.606*/607TriggerStep *sqlite3TriggerDeleteStep(608 Parse *pParse, /* Parser */609 SrcList *pTabList, /* The table from which rows are deleted */610 Expr *pWhere, /* The WHERE clause */611 const char *zStart, /* Start of SQL text */612 const char *zEnd /* End of SQL text */613){614 sqlite3 *db = pParse->db;615 TriggerStep *pTriggerStep;616 617 pTriggerStep = triggerStepAllocate(pParse, TK_DELETE, pTabList, zStart, zEnd);618 if( pTriggerStep ){619 if( IN_RENAME_OBJECT ){620 pTriggerStep->pWhere = pWhere;621 pWhere = 0;622 }else{623 pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);624 }625 pTriggerStep->orconf = OE_Default;626 }627 sqlite3ExprDelete(db, pWhere);628 return pTriggerStep;629}630 631/* 632** Recursively delete a Trigger structure633*/634void sqlite3DeleteTrigger(sqlite3 *db, Trigger *pTrigger){635 if( pTrigger==0 || pTrigger->bReturning ) return;636 sqlite3DeleteTriggerStep(db, pTrigger->step_list);637 sqlite3DbFree(db, pTrigger->zName);638 sqlite3DbFree(db, pTrigger->table);639 sqlite3ExprDelete(db, pTrigger->pWhen);640 sqlite3IdListDelete(db, pTrigger->pColumns);641 sqlite3DbFree(db, pTrigger);642}643 644/*645** This function is called to drop a trigger from the database schema. 646**647** This may be called directly from the parser and therefore identifies648** the trigger by name. The sqlite3DropTriggerPtr() routine does the649** same job as this routine except it takes a pointer to the trigger650** instead of the trigger name.651**/652void sqlite3DropTrigger(Parse *pParse, SrcList *pName, int noErr){653 Trigger *pTrigger = 0;654 int i;655 const char *zDb;656 const char *zName;657 sqlite3 *db = pParse->db;658 659 if( db->mallocFailed ) goto drop_trigger_cleanup;660 if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){661 goto drop_trigger_cleanup;662 }663 664 assert( pName->nSrc==1 );665 assert( pName->a[0].fg.fixedSchema==0 && pName->a[0].fg.isSubquery==0 );666 zDb = pName->a[0].u4.zDatabase;667 zName = pName->a[0].zName;668 assert( zDb!=0 || sqlite3BtreeHoldsAllMutexes(db) );669 for(i=OMIT_TEMPDB; i<db->nDb; i++){670 int j = (i<2) ? i^1 : i; /* Search TEMP before MAIN */671 if( zDb && sqlite3DbIsNamed(db, j, zDb)==0 ) continue;672 assert( sqlite3SchemaMutexHeld(db, j, 0) );673 pTrigger = sqlite3HashFind(&(db->aDb[j].pSchema->trigHash), zName);674 if( pTrigger ) break;675 }676 if( !pTrigger ){677 if( !noErr ){678 sqlite3ErrorMsg(pParse, "no such trigger: %S", pName->a);679 }else{680 sqlite3CodeVerifyNamedSchema(pParse, zDb);681 }682 pParse->checkSchema = 1;683 goto drop_trigger_cleanup;684 }685 sqlite3DropTriggerPtr(pParse, pTrigger);686 687drop_trigger_cleanup:688 sqlite3SrcListDelete(db, pName);689}690 691/*692** Return a pointer to the Table structure for the table that a trigger693** is set on.694*/695static Table *tableOfTrigger(Trigger *pTrigger){696 return sqlite3HashFind(&pTrigger->pTabSchema->tblHash, pTrigger->table);697}698 699 700/*701** Drop a trigger given a pointer to that trigger. 702*/703void sqlite3DropTriggerPtr(Parse *pParse, Trigger *pTrigger){704 Table *pTable;705 Vdbe *v;706 sqlite3 *db = pParse->db;707 int iDb;708 709 iDb = sqlite3SchemaToIndex(pParse->db, pTrigger->pSchema);710 assert( iDb>=0 && iDb<db->nDb );711 pTable = tableOfTrigger(pTrigger);712 assert( (pTable && pTable->pSchema==pTrigger->pSchema) || iDb==1 );713#ifndef SQLITE_OMIT_AUTHORIZATION714 if( pTable ){715 int code = SQLITE_DROP_TRIGGER;716 const char *zDb = db->aDb[iDb].zDbSName;717 const char *zTab = SCHEMA_TABLE(iDb);718 if( iDb==1 ) code = SQLITE_DROP_TEMP_TRIGGER;719 if( sqlite3AuthCheck(pParse, code, pTrigger->zName, pTable->zName, zDb) ||720 sqlite3AuthCheck(pParse, SQLITE_DELETE, zTab, 0, zDb) ){721 return;722 }723 }724#endif725 726 /* Generate code to destroy the database record of the trigger.727 */728 if( (v = sqlite3GetVdbe(pParse))!=0 ){729 sqlite3NestedParse(pParse,730 "DELETE FROM %Q." LEGACY_SCHEMA_TABLE " WHERE name=%Q AND type='trigger'",731 db->aDb[iDb].zDbSName, pTrigger->zName732 );733 sqlite3ChangeCookie(pParse, iDb);734 sqlite3VdbeAddOp4(v, OP_DropTrigger, iDb, 0, 0, pTrigger->zName, 0);735 }736}737 738/*739** Remove a trigger from the hash tables of the sqlite* pointer.740*/741void sqlite3UnlinkAndDeleteTrigger(sqlite3 *db, int iDb, const char *zName){742 Trigger *pTrigger;743 Hash *pHash;744 745 assert( sqlite3SchemaMutexHeld(db, iDb, 0) );746 pHash = &(db->aDb[iDb].pSchema->trigHash);747 pTrigger = sqlite3HashInsert(pHash, zName, 0);748 if( ALWAYS(pTrigger) ){749 if( pTrigger->pSchema==pTrigger->pTabSchema ){750 Table *pTab = tableOfTrigger(pTrigger);751 if( pTab ){752 Trigger **pp;753 for(pp=&pTab->pTrigger; *pp; pp=&((*pp)->pNext)){754 if( *pp==pTrigger ){755 *pp = (*pp)->pNext;756 break;757 }758 }759 }760 }761 sqlite3DeleteTrigger(db, pTrigger);762 db->mDbFlags |= DBFLAG_SchemaChange;763 }764}765 766/*767** pEList is the SET clause of an UPDATE statement. Each entry768** in pEList is of the format <id>=<expr>. If any of the entries769** in pEList have an <id> which matches an identifier in pIdList,770** then return TRUE. If pIdList==NULL, then it is considered a771** wildcard that matches anything. Likewise if pEList==NULL then772** it matches anything so always return true. Return false only773** if there is no match.774*/775static int checkColumnOverlap(IdList *pIdList, ExprList *pEList){776 int e;777 if( pIdList==0 || NEVER(pEList==0) ) return 1;778 for(e=0; e<pEList->nExpr; e++){779 if( sqlite3IdListIndex(pIdList, pEList->a[e].zEName)>=0 ) return 1;780 }781 return 0; 782}783 784/*785** Return true if any TEMP triggers exist786*/787static int tempTriggersExist(sqlite3 *db){788 if( NEVER(db->aDb[1].pSchema==0) ) return 0;789 if( sqliteHashFirst(&db->aDb[1].pSchema->trigHash)==0 ) return 0;790 return 1;791}792 793/*794** Return a list of all triggers on table pTab if there exists at least795** one trigger that must be fired when an operation of type 'op' is 796** performed on the table, and, if that operation is an UPDATE, if at797** least one of the columns in pChanges is being modified.798*/799static SQLITE_NOINLINE Trigger *triggersReallyExist(800 Parse *pParse, /* Parse context */801 Table *pTab, /* The table the contains the triggers */802 int op, /* one of TK_DELETE, TK_INSERT, TK_UPDATE */803 ExprList *pChanges, /* Columns that change in an UPDATE statement */804 int *pMask /* OUT: Mask of TRIGGER_BEFORE|TRIGGER_AFTER */805){806 int mask = 0;807 Trigger *pList = 0;808 Trigger *p;809 810 pList = sqlite3TriggerList(pParse, pTab);811 assert( pList==0 || IsVirtual(pTab)==0 812 || (pList->bReturning && pList->pNext==0) );813 if( pList!=0 ){814 p = pList;815 if( (pParse->db->flags & SQLITE_EnableTrigger)==0816 && pTab->pTrigger!=0817 ){818 /* The SQLITE_DBCONFIG_ENABLE_TRIGGER setting is off. That means that819 ** only TEMP triggers are allowed. Truncate the pList so that it820 ** includes only TEMP triggers */821 if( pList==pTab->pTrigger ){822 pList = 0;823 goto exit_triggers_exist;824 }825 while( ALWAYS(p->pNext) && p->pNext!=pTab->pTrigger ) p = p->pNext;826 p->pNext = 0;827 p = pList;828 }829 do{830 if( p->op==op && checkColumnOverlap(p->pColumns, pChanges) ){831 mask |= p->tr_tm;832 }else if( p->op==TK_RETURNING ){833 /* The first time a RETURNING trigger is seen, the "op" value tells834 ** us what time of trigger it should be. */835 assert( sqlite3IsToplevel(pParse) );836 p->op = op;837 if( IsVirtual(pTab) ){838 if( op!=TK_INSERT ){839 sqlite3ErrorMsg(pParse,840 "%s RETURNING is not available on virtual tables",841 op==TK_DELETE ? "DELETE" : "UPDATE");842 }843 p->tr_tm = TRIGGER_BEFORE;844 }else{845 p->tr_tm = TRIGGER_AFTER;846 }847 mask |= p->tr_tm;848 }else if( p->bReturning && p->op==TK_INSERT && op==TK_UPDATE849 && sqlite3IsToplevel(pParse) ){850 /* Also fire a RETURNING trigger for an UPSERT */851 mask |= p->tr_tm;852 }853 p = p->pNext;854 }while( p );855 }856exit_triggers_exist:857 if( pMask ){858 *pMask = mask;859 }860 return (mask ? pList : 0);861}862Trigger *sqlite3TriggersExist(863 Parse *pParse, /* Parse context */864 Table *pTab, /* The table the contains the triggers */865 int op, /* one of TK_DELETE, TK_INSERT, TK_UPDATE */866 ExprList *pChanges, /* Columns that change in an UPDATE statement */867 int *pMask /* OUT: Mask of TRIGGER_BEFORE|TRIGGER_AFTER */868){869 assert( pTab!=0 );870 if( (pTab->pTrigger==0 && !tempTriggersExist(pParse->db))871 || pParse->disableTriggers872 ){873 if( pMask ) *pMask = 0;874 return 0;875 }876 return triggersReallyExist(pParse,pTab,op,pChanges,pMask);877}878 879/*880** Return true if the pExpr term from the RETURNING clause argument881** list is of the form "*". Raise an error if the terms if of the882** form "table.*".883*/884static int isAsteriskTerm(885 Parse *pParse, /* Parsing context */886 Expr *pTerm /* A term in the RETURNING clause */887){888 assert( pTerm!=0 );889 if( pTerm->op==TK_ASTERISK ) return 1;890 if( pTerm->op!=TK_DOT ) return 0;891 assert( pTerm->pRight!=0 );892 assert( pTerm->pLeft!=0 );893 if( pTerm->pRight->op!=TK_ASTERISK ) return 0;894 sqlite3ErrorMsg(pParse, "RETURNING may not use \"TABLE.*\" wildcards");895 return 1;896}897 898/* The input list pList is the list of result set terms from a RETURNING899** clause. The table that we are returning from is pTab.900**901** This routine makes a copy of the pList, and at the same time expands902** any "*" wildcards to be the complete set of columns from pTab.903*/904static ExprList *sqlite3ExpandReturning(905 Parse *pParse, /* Parsing context */906 ExprList *pList, /* The arguments to RETURNING */907 Table *pTab /* The table being updated */908){909 ExprList *pNew = 0;910 sqlite3 *db = pParse->db;911 int i;912 913 for(i=0; i<pList->nExpr; i++){914 Expr *pOldExpr = pList->a[i].pExpr;915 if( NEVER(pOldExpr==0) ) continue;916 if( isAsteriskTerm(pParse, pOldExpr) ){917 int jj;918 for(jj=0; jj<pTab->nCol; jj++){919 Expr *pNewExpr;920 if( IsHiddenColumn(pTab->aCol+jj) ) continue;921 pNewExpr = sqlite3Expr(db, TK_ID, pTab->aCol[jj].zCnName);922 pNew = sqlite3ExprListAppend(pParse, pNew, pNewExpr);923 if( !db->mallocFailed ){924 struct ExprList_item *pItem = &pNew->a[pNew->nExpr-1];925 pItem->zEName = sqlite3DbStrDup(db, pTab->aCol[jj].zCnName);926 pItem->fg.eEName = ENAME_NAME;927 }928 }929 }else{930 Expr *pNewExpr = sqlite3ExprDup(db, pOldExpr, 0);931 pNew = sqlite3ExprListAppend(pParse, pNew, pNewExpr);932 if( !db->mallocFailed && ALWAYS(pList->a[i].zEName!=0) ){933 struct ExprList_item *pItem = &pNew->a[pNew->nExpr-1];934 pItem->zEName = sqlite3DbStrDup(db, pList->a[i].zEName);935 pItem->fg.eEName = pList->a[i].fg.eEName;936 }937 }938 }939 return pNew;940}941 942/* If the Expr node is a subquery or an EXISTS operator or an IN operator that943** uses a subquery, and if the subquery is SF_Correlated, then mark the944** expression as EP_VarSelect.945*/946static int sqlite3ReturningSubqueryVarSelect(Walker *NotUsed, Expr *pExpr){947 UNUSED_PARAMETER(NotUsed);948 if( ExprUseXSelect(pExpr)949 && (pExpr->x.pSelect->selFlags & SF_Correlated)!=0950 ){951 testcase( ExprHasProperty(pExpr, EP_VarSelect) );952 ExprSetProperty(pExpr, EP_VarSelect);953 }954 return WRC_Continue;955}956 957 958/*959** If the SELECT references the table pWalker->u.pTab, then do two things:960**961** (1) Mark the SELECT as as SF_Correlated.962** (2) Set pWalker->eCode to non-zero so that the caller will know963** that (1) has happened.964*/965static int sqlite3ReturningSubqueryCorrelated(Walker *pWalker, Select *pSelect){966 int i;967 SrcList *pSrc;968 assert( pSelect!=0 );969 pSrc = pSelect->pSrc;970 assert( pSrc!=0 );971 for(i=0; i<pSrc->nSrc; i++){972 if( pSrc->a[i].pSTab==pWalker->u.pTab ){973 testcase( pSelect->selFlags & SF_Correlated );974 pSelect->selFlags |= SF_Correlated;975 pWalker->eCode = 1;976 break;977 }978 }979 return WRC_Continue;980}981 982/*983** Scan the expression list that is the argument to RETURNING looking984** for subqueries that depend on the table which is being modified in the985** statement that is hosting the RETURNING clause (pTab). Mark all such986** subqueries as SF_Correlated. If the subqueries are part of an987** expression, mark the expression as EP_VarSelect.988**989** https://sqlite.org/forum/forumpost/2c83569ce8945d39990*/991static void sqlite3ProcessReturningSubqueries(992 ExprList *pEList,993 Table *pTab994){995 Walker w;996 memset(&w, 0, sizeof(w));997 w.xExprCallback = sqlite3ExprWalkNoop;998 w.xSelectCallback = sqlite3ReturningSubqueryCorrelated;999 w.u.pTab = pTab;1000 sqlite3WalkExprList(&w, pEList);1001 if( w.eCode ){1002 w.xExprCallback = sqlite3ReturningSubqueryVarSelect;1003 w.xSelectCallback = sqlite3SelectWalkNoop;1004 sqlite3WalkExprList(&w, pEList);1005 }1006}1007 1008/*1009** Generate code for the RETURNING trigger. Unlike other triggers1010** that invoke a subprogram in the bytecode, the code for RETURNING1011** is generated in-line.1012*/1013static void codeReturningTrigger(1014 Parse *pParse, /* Parse context */1015 Trigger *pTrigger, /* The trigger step that defines the RETURNING */1016 Table *pTab, /* The table to code triggers from */1017 int regIn /* The first in an array of registers */1018){1019 Vdbe *v = pParse->pVdbe;1020 sqlite3 *db = pParse->db;1021 ExprList *pNew;1022 Returning *pReturning;1023 Select sSelect;1024 SrcList *pFrom;1025 union {1026 SrcList sSrc;1027 u8 fromSpace[SZ_SRCLIST_1];1028 } uSrc;1029 1030 assert( v!=0 );1031 if( !pParse->bReturning ){1032 /* This RETURNING trigger must be for a different statement as1033 ** this statement lacks a RETURNING clause. */1034 return;1035 }1036 assert( db->pParse==pParse );1037 assert( !pParse->isCreate );1038 pReturning = pParse->u1.d.pReturning;1039 if( pTrigger != &(pReturning->retTrig) ){1040 /* This RETURNING trigger is for a different statement */1041 return;1042 }1043 memset(&sSelect, 0, sizeof(sSelect));1044 memset(&uSrc, 0, sizeof(uSrc));1045 pFrom = &uSrc.sSrc;1046 sSelect.pEList = sqlite3ExprListDup(db, pReturning->pReturnEL, 0);1047 sSelect.pSrc = pFrom;1048 pFrom->nSrc = 1;1049 pFrom->a[0].pSTab = pTab;1050 pFrom->a[0].zName = pTab->zName; /* tag-20240424-1 */1051 pFrom->a[0].iCursor = -1;1052 sqlite3SelectPrep(pParse, &sSelect, 0);1053 if( pParse->nErr==0 ){1054 assert( db->mallocFailed==0 );1055 sqlite3GenerateColumnNames(pParse, &sSelect);1056 }1057 sqlite3ExprListDelete(db, sSelect.pEList);1058 pNew = sqlite3ExpandReturning(pParse, pReturning->pReturnEL, pTab);1059 if( pParse->nErr==0 ){1060 NameContext sNC;1061 memset(&sNC, 0, sizeof(sNC));1062 if( pReturning->nRetCol==0 ){1063 pReturning->nRetCol = pNew->nExpr;1064 pReturning->iRetCur = pParse->nTab++;1065 }1066 sNC.pParse = pParse;1067 sNC.uNC.iBaseReg = regIn;1068 sNC.ncFlags = NC_UBaseReg;1069 pParse->eTriggerOp = pTrigger->op;1070 pParse->pTriggerTab = pTab;1071 if( sqlite3ResolveExprListNames(&sNC, pNew)==SQLITE_OK1072 && ALWAYS(!db->mallocFailed)1073 ){1074 int i;1075 int nCol = pNew->nExpr;1076 int reg = pParse->nMem+1;1077 sqlite3ProcessReturningSubqueries(pNew, pTab);1078 pParse->nMem += nCol+2;1079 pReturning->iRetReg = reg;1080 for(i=0; i<nCol; i++){1081 Expr *pCol = pNew->a[i].pExpr;1082 assert( pCol!=0 ); /* Due to !db->mallocFailed ~9 lines above */1083 sqlite3ExprCodeFactorable(pParse, pCol, reg+i);1084 if( sqlite3ExprAffinity(pCol)==SQLITE_AFF_REAL ){1085 sqlite3VdbeAddOp1(v, OP_RealAffinity, reg+i);1086 }1087 }1088 sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, i, reg+i);1089 sqlite3VdbeAddOp2(v, OP_NewRowid, pReturning->iRetCur, reg+i+1);1090 sqlite3VdbeAddOp3(v, OP_Insert, pReturning->iRetCur, reg+i, reg+i+1);1091 }1092 }1093 sqlite3ExprListDelete(db, pNew);1094 pParse->eTriggerOp = 0;1095 pParse->pTriggerTab = 0;1096}1097 1098 1099 1100/*1101** Generate VDBE code for the statements inside the body of a single 1102** trigger.1103*/1104static int codeTriggerProgram(1105 Parse *pParse, /* The parser context */1106 TriggerStep *pStepList, /* List of statements inside the trigger body */1107 int orconf /* Conflict algorithm. (OE_Abort, etc) */ 1108){1109 TriggerStep *pStep;1110 Vdbe *v = pParse->pVdbe;1111 sqlite3 *db = pParse->db;1112 1113 assert( pParse->pTriggerTab && pParse->pToplevel );1114 assert( pStepList );1115 assert( v!=0 );1116 for(pStep=pStepList; pStep; pStep=pStep->pNext){1117 /* Figure out the ON CONFLICT policy that will be used for this step1118 ** of the trigger program. If the statement that caused this trigger1119 ** to fire had an explicit ON CONFLICT, then use it. Otherwise, use1120 ** the ON CONFLICT policy that was specified as part of the trigger1121 ** step statement. Example:1122 **1123 ** CREATE TRIGGER AFTER INSERT ON t1 BEGIN;1124 ** INSERT OR REPLACE INTO t2 VALUES(new.a, new.b);1125 ** END;1126 **1127 ** INSERT INTO t1 ... ; -- insert into t2 uses REPLACE policy1128 ** INSERT OR IGNORE INTO t1 ... ; -- insert into t2 uses IGNORE policy1129 */1130 pParse->eOrconf = (orconf==OE_Default)?pStep->orconf:(u8)orconf;1131 assert( pParse->okConstFactor==0 );1132 1133#ifndef SQLITE_OMIT_TRACE1134 if( pStep->zSpan ){1135 sqlite3VdbeAddOp4(v, OP_Trace, 0x7fffffff, 1, 0,1136 sqlite3MPrintf(db, "-- %s", pStep->zSpan),1137 P4_DYNAMIC);1138 }1139#endif1140 1141 switch( pStep->op ){1142 case TK_UPDATE: {1143 sqlite3Update(pParse, 1144 sqlite3SrcListDup(db, pStep->pSrc, 0),1145 sqlite3ExprListDup(db, pStep->pExprList, 0), 1146 sqlite3ExprDup(db, pStep->pWhere, 0), 1147 pParse->eOrconf, 0, 0, 01148 );1149 sqlite3VdbeAddOp0(v, OP_ResetCount);1150 break;1151 }1152 case TK_INSERT: {1153 sqlite3Insert(pParse, 1154 sqlite3SrcListDup(db, pStep->pSrc, 0),1155 sqlite3SelectDup(db, pStep->pSelect, 0), 1156 sqlite3IdListDup(db, pStep->pIdList), 1157 pParse->eOrconf,1158 sqlite3UpsertDup(db, pStep->pUpsert)1159 );1160 sqlite3VdbeAddOp0(v, OP_ResetCount);1161 break;1162 }1163 case TK_DELETE: {1164 sqlite3DeleteFrom(pParse, 1165 sqlite3SrcListDup(db, pStep->pSrc, 0),1166 sqlite3ExprDup(db, pStep->pWhere, 0), 0, 01167 );1168 sqlite3VdbeAddOp0(v, OP_ResetCount);1169 break;1170 }1171 default: assert( pStep->op==TK_SELECT ); {1172 SelectDest sDest;1173 Select *pSelect = sqlite3SelectDup(db, pStep->pSelect, 0);1174 sqlite3SelectDestInit(&sDest, SRT_Discard, 0);1175 sqlite3Select(pParse, pSelect, &sDest);1176 sqlite3SelectDelete(db, pSelect);1177 break;1178 }1179 } 1180 }1181 1182 return 0;1183}1184 1185#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS1186/*1187** This function is used to add VdbeComment() annotations to a VDBE1188** program. It is not used in production code, only for debugging.1189*/1190static const char *onErrorText(int onError){1191 switch( onError ){1192 case OE_Abort: return "abort";1193 case OE_Rollback: return "rollback";1194 case OE_Fail: return "fail";1195 case OE_Replace: return "replace";1196 case OE_Ignore: return "ignore";1197 case OE_Default: return "default";1198 }1199 return "n/a";1200}