3 ** The author disclaims copyright to this source code. In place of
4 ** a legal notice, here is a blessing:
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.
10 *************************************************************************
11 ** This file contains the implementation for TRIGGERs
13 #include "sqliteInt.h"
15 #ifndef SQLITE_OMIT_TRIGGER
17 ** Delete a linked list of TriggerStep structures.
19 void sqlite3DeleteTriggerStep(sqlite3
*db
, TriggerStep
*pTriggerStep
){
20 while( pTriggerStep
){
21 TriggerStep
* pTmp
= pTriggerStep
;
22 pTriggerStep
= pTriggerStep
->pNext
;
24 sqlite3ExprDelete(db
, pTmp
->pWhere
);
25 sqlite3ExprListDelete(db
, pTmp
->pExprList
);
26 sqlite3SelectDelete(db
, pTmp
->pSelect
);
27 sqlite3IdListDelete(db
, pTmp
->pIdList
);
29 sqlite3DbFree(db
, pTmp
);
34 ** Given table pTab, return a list of all the triggers attached to
35 ** the table. The list is connected by Trigger.pNext pointers.
37 ** All of the triggers on pTab that are in the same database as pTab
38 ** are already attached to pTab->pTrigger. But there might be additional
39 ** triggers on pTab in the TEMP schema. This routine prepends all
40 ** TEMP triggers on pTab to the beginning of the pTab->pTrigger list
41 ** and returns the combined list.
43 ** To state it another way: This routine returns a list of all triggers
44 ** that fire off of pTab. The list will include any TEMP triggers on
45 ** pTab as well as the triggers lised in pTab->pTrigger.
47 Trigger
*sqlite3TriggerList(Parse
*pParse
, Table
*pTab
){
48 Schema
* const pTmpSchema
= pParse
->db
->aDb
[1].pSchema
;
49 Trigger
*pList
= 0; /* List of triggers to return */
51 if( pParse
->disableTriggers
){
55 if( pTmpSchema
!=pTab
->pSchema
){
57 assert( sqlite3SchemaMutexHeld(pParse
->db
, 0, pTmpSchema
) );
58 for(p
=sqliteHashFirst(&pTmpSchema
->trigHash
); p
; p
=sqliteHashNext(p
)){
59 Trigger
*pTrig
= (Trigger
*)sqliteHashData(p
);
60 if( pTrig
->pTabSchema
==pTab
->pSchema
61 && 0==sqlite3StrICmp(pTrig
->table
, pTab
->zName
)
63 pTrig
->pNext
= (pList
? pList
: pTab
->pTrigger
);
69 return (pList
? pList
: pTab
->pTrigger
);
73 ** This is called by the parser when it sees a CREATE TRIGGER statement
74 ** up to the point of the BEGIN before the trigger actions. A Trigger
75 ** structure is generated based on the information available and stored
76 ** in pParse->pNewTrigger. After the trigger actions have been parsed, the
77 ** sqlite3FinishTrigger() function is called to complete the trigger
78 ** construction process.
80 void sqlite3BeginTrigger(
81 Parse
*pParse
, /* The parse context of the CREATE TRIGGER statement */
82 Token
*pName1
, /* The name of the trigger */
83 Token
*pName2
, /* The name of the trigger */
84 int tr_tm
, /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD */
85 int op
, /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
86 IdList
*pColumns
, /* column list if this is an UPDATE OF trigger */
87 SrcList
*pTableName
,/* The name of the table/view the trigger applies to */
88 Expr
*pWhen
, /* WHEN clause */
89 int isTemp
, /* True if the TEMPORARY keyword is present */
90 int noErr
/* Suppress errors if the trigger already exists */
92 Trigger
*pTrigger
= 0; /* The new trigger */
93 Table
*pTab
; /* Table that the trigger fires off of */
94 char *zName
= 0; /* Name of the trigger */
95 sqlite3
*db
= pParse
->db
; /* The database connection */
96 int iDb
; /* The database to store the trigger in */
97 Token
*pName
; /* The unqualified db name */
98 DbFixer sFix
; /* State vector for the DB fixer */
100 assert( pName1
!=0 ); /* pName1->z might be NULL, but not pName1 itself */
102 assert( op
==TK_INSERT
|| op
==TK_UPDATE
|| op
==TK_DELETE
);
103 assert( op
>0 && op
<0xff );
105 /* If TEMP was specified, then the trigger name may not be qualified. */
107 sqlite3ErrorMsg(pParse
, "temporary trigger may not have qualified name");
108 goto trigger_cleanup
;
113 /* Figure out the db that the trigger will be created in */
114 iDb
= sqlite3TwoPartName(pParse
, pName1
, pName2
, &pName
);
116 goto trigger_cleanup
;
119 if( !pTableName
|| db
->mallocFailed
){
120 goto trigger_cleanup
;
123 /* A long-standing parser bug is that this syntax was allowed:
125 ** CREATE TRIGGER attached.demo AFTER INSERT ON attached.tab ....
128 ** To maintain backwards compatibility, ignore the database
129 ** name on pTableName if we are reparsing out of SQLITE_MASTER.
131 if( db
->init
.busy
&& iDb
!=1 ){
132 sqlite3DbFree(db
, pTableName
->a
[0].zDatabase
);
133 pTableName
->a
[0].zDatabase
= 0;
136 /* If the trigger name was unqualified, and the table is a temp table,
137 ** then set iDb to 1 to create the trigger in the temporary database.
138 ** If sqlite3SrcListLookup() returns 0, indicating the table does not
139 ** exist, the error is caught by the block below.
141 pTab
= sqlite3SrcListLookup(pParse
, pTableName
);
142 if( db
->init
.busy
==0 && pName2
->n
==0 && pTab
143 && pTab
->pSchema
==db
->aDb
[1].pSchema
){
147 /* Ensure the table name matches database name and that the table exists */
148 if( db
->mallocFailed
) goto trigger_cleanup
;
149 assert( pTableName
->nSrc
==1 );
150 sqlite3FixInit(&sFix
, pParse
, iDb
, "trigger", pName
);
151 if( sqlite3FixSrcList(&sFix
, pTableName
) ){
152 goto trigger_cleanup
;
154 pTab
= sqlite3SrcListLookup(pParse
, pTableName
);
156 /* The table does not exist. */
157 if( db
->init
.iDb
==1 ){
159 ** Normally, whenever a table is dropped, all associated triggers are
160 ** dropped too. But if a TEMP trigger is created on a non-TEMP table
161 ** and the table is dropped by a different database connection, the
162 ** trigger is not visible to the database connection that does the
163 ** drop so the trigger cannot be dropped. This results in an
164 ** "orphaned trigger" - a trigger whose associated table is missing.
166 db
->init
.orphanTrigger
= 1;
168 goto trigger_cleanup
;
170 if( IsVirtual(pTab
) ){
171 sqlite3ErrorMsg(pParse
, "cannot create triggers on virtual tables");
172 goto trigger_cleanup
;
175 /* Check that the trigger name is not reserved and that no trigger of the
176 ** specified name exists */
177 zName
= sqlite3NameFromToken(db
, pName
);
178 if( !zName
|| SQLITE_OK
!=sqlite3CheckObjectName(pParse
, zName
) ){
179 goto trigger_cleanup
;
181 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
182 if( sqlite3HashFind(&(db
->aDb
[iDb
].pSchema
->trigHash
),zName
) ){
184 sqlite3ErrorMsg(pParse
, "trigger %T already exists", pName
);
186 assert( !db
->init
.busy
);
187 sqlite3CodeVerifySchema(pParse
, iDb
);
189 goto trigger_cleanup
;
192 /* Do not create a trigger on a system table */
193 if( sqlite3StrNICmp(pTab
->zName
, "sqlite_", 7)==0 ){
194 sqlite3ErrorMsg(pParse
, "cannot create trigger on system table");
195 goto trigger_cleanup
;
198 /* INSTEAD of triggers are only for views and views only support INSTEAD
201 if( pTab
->pSelect
&& tr_tm
!=TK_INSTEAD
){
202 sqlite3ErrorMsg(pParse
, "cannot create %s trigger on view: %S",
203 (tr_tm
== TK_BEFORE
)?"BEFORE":"AFTER", pTableName
, 0);
204 goto trigger_cleanup
;
206 if( !pTab
->pSelect
&& tr_tm
==TK_INSTEAD
){
207 sqlite3ErrorMsg(pParse
, "cannot create INSTEAD OF"
208 " trigger on table: %S", pTableName
, 0);
209 goto trigger_cleanup
;
212 #ifndef SQLITE_OMIT_AUTHORIZATION
214 int iTabDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
215 int code
= SQLITE_CREATE_TRIGGER
;
216 const char *zDb
= db
->aDb
[iTabDb
].zDbSName
;
217 const char *zDbTrig
= isTemp
? db
->aDb
[1].zDbSName
: zDb
;
218 if( iTabDb
==1 || isTemp
) code
= SQLITE_CREATE_TEMP_TRIGGER
;
219 if( sqlite3AuthCheck(pParse
, code
, zName
, pTab
->zName
, zDbTrig
) ){
220 goto trigger_cleanup
;
222 if( sqlite3AuthCheck(pParse
, SQLITE_INSERT
, SCHEMA_TABLE(iTabDb
),0,zDb
)){
223 goto trigger_cleanup
;
228 /* INSTEAD OF triggers can only appear on views and BEFORE triggers
229 ** cannot appear on views. So we might as well translate every
230 ** INSTEAD OF trigger into a BEFORE trigger. It simplifies code
233 if (tr_tm
== TK_INSTEAD
){
237 /* Build the Trigger object */
238 pTrigger
= (Trigger
*)sqlite3DbMallocZero(db
, sizeof(Trigger
));
239 if( pTrigger
==0 ) goto trigger_cleanup
;
240 pTrigger
->zName
= zName
;
242 pTrigger
->table
= sqlite3DbStrDup(db
, pTableName
->a
[0].zName
);
243 pTrigger
->pSchema
= db
->aDb
[iDb
].pSchema
;
244 pTrigger
->pTabSchema
= pTab
->pSchema
;
245 pTrigger
->op
= (u8
)op
;
246 pTrigger
->tr_tm
= tr_tm
==TK_BEFORE
? TRIGGER_BEFORE
: TRIGGER_AFTER
;
247 pTrigger
->pWhen
= sqlite3ExprDup(db
, pWhen
, EXPRDUP_REDUCE
);
248 pTrigger
->pColumns
= sqlite3IdListDup(db
, pColumns
);
249 assert( pParse
->pNewTrigger
==0 );
250 pParse
->pNewTrigger
= pTrigger
;
253 sqlite3DbFree(db
, zName
);
254 sqlite3SrcListDelete(db
, pTableName
);
255 sqlite3IdListDelete(db
, pColumns
);
256 sqlite3ExprDelete(db
, pWhen
);
257 if( !pParse
->pNewTrigger
){
258 sqlite3DeleteTrigger(db
, pTrigger
);
260 assert( pParse
->pNewTrigger
==pTrigger
);
265 ** This routine is called after all of the trigger actions have been parsed
266 ** in order to complete the process of building the trigger.
268 void sqlite3FinishTrigger(
269 Parse
*pParse
, /* Parser context */
270 TriggerStep
*pStepList
, /* The triggered program */
271 Token
*pAll
/* Token that describes the complete CREATE TRIGGER */
273 Trigger
*pTrig
= pParse
->pNewTrigger
; /* Trigger being finished */
274 char *zName
; /* Name of trigger */
275 sqlite3
*db
= pParse
->db
; /* The database */
276 DbFixer sFix
; /* Fixer object */
277 int iDb
; /* Database containing the trigger */
278 Token nameToken
; /* Trigger name for error reporting */
280 pParse
->pNewTrigger
= 0;
281 if( NEVER(pParse
->nErr
) || !pTrig
) goto triggerfinish_cleanup
;
282 zName
= pTrig
->zName
;
283 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTrig
->pSchema
);
284 pTrig
->step_list
= pStepList
;
286 pStepList
->pTrig
= pTrig
;
287 pStepList
= pStepList
->pNext
;
289 sqlite3TokenInit(&nameToken
, pTrig
->zName
);
290 sqlite3FixInit(&sFix
, pParse
, iDb
, "trigger", &nameToken
);
291 if( sqlite3FixTriggerStep(&sFix
, pTrig
->step_list
)
292 || sqlite3FixExpr(&sFix
, pTrig
->pWhen
)
294 goto triggerfinish_cleanup
;
297 /* if we are not initializing,
298 ** build the sqlite_master entry
300 if( !db
->init
.busy
){
304 /* Make an entry in the sqlite_master table */
305 v
= sqlite3GetVdbe(pParse
);
306 if( v
==0 ) goto triggerfinish_cleanup
;
307 sqlite3BeginWriteOperation(pParse
, 0, iDb
);
308 z
= sqlite3DbStrNDup(db
, (char*)pAll
->z
, pAll
->n
);
310 sqlite3NestedParse(pParse
,
311 "INSERT INTO %Q.%s VALUES('trigger',%Q,%Q,0,'CREATE TRIGGER %q')",
312 db
->aDb
[iDb
].zDbSName
, MASTER_NAME
, zName
,
314 sqlite3DbFree(db
, z
);
315 sqlite3ChangeCookie(pParse
, iDb
);
316 sqlite3VdbeAddParseSchemaOp(v
, iDb
,
317 sqlite3MPrintf(db
, "type='trigger' AND name='%q'", zName
));
321 Trigger
*pLink
= pTrig
;
322 Hash
*pHash
= &db
->aDb
[iDb
].pSchema
->trigHash
;
323 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
324 pTrig
= sqlite3HashInsert(pHash
, zName
, pTrig
);
327 }else if( pLink
->pSchema
==pLink
->pTabSchema
){
329 pTab
= sqlite3HashFind(&pLink
->pTabSchema
->tblHash
, pLink
->table
);
331 pLink
->pNext
= pTab
->pTrigger
;
332 pTab
->pTrigger
= pLink
;
336 triggerfinish_cleanup
:
337 sqlite3DeleteTrigger(db
, pTrig
);
338 assert( !pParse
->pNewTrigger
);
339 sqlite3DeleteTriggerStep(db
, pStepList
);
343 ** Turn a SELECT statement (that the pSelect parameter points to) into
344 ** a trigger step. Return a pointer to a TriggerStep structure.
346 ** The parser calls this routine when it finds a SELECT statement in
347 ** body of a TRIGGER.
349 TriggerStep
*sqlite3TriggerSelectStep(sqlite3
*db
, Select
*pSelect
){
350 TriggerStep
*pTriggerStep
= sqlite3DbMallocZero(db
, sizeof(TriggerStep
));
351 if( pTriggerStep
==0 ) {
352 sqlite3SelectDelete(db
, pSelect
);
355 pTriggerStep
->op
= TK_SELECT
;
356 pTriggerStep
->pSelect
= pSelect
;
357 pTriggerStep
->orconf
= OE_Default
;
362 ** Allocate space to hold a new trigger step. The allocated space
363 ** holds both the TriggerStep object and the TriggerStep.target.z string.
365 ** If an OOM error occurs, NULL is returned and db->mallocFailed is set.
367 static TriggerStep
*triggerStepAllocate(
368 sqlite3
*db
, /* Database connection */
369 u8 op
, /* Trigger opcode */
370 Token
*pName
/* The target name */
372 TriggerStep
*pTriggerStep
;
374 pTriggerStep
= sqlite3DbMallocZero(db
, sizeof(TriggerStep
) + pName
->n
+ 1);
376 char *z
= (char*)&pTriggerStep
[1];
377 memcpy(z
, pName
->z
, pName
->n
);
379 pTriggerStep
->zTarget
= z
;
380 pTriggerStep
->op
= op
;
386 ** Build a trigger step out of an INSERT statement. Return a pointer
387 ** to the new trigger step.
389 ** The parser calls this routine when it sees an INSERT inside the
390 ** body of a trigger.
392 TriggerStep
*sqlite3TriggerInsertStep(
393 sqlite3
*db
, /* The database connection */
394 Token
*pTableName
, /* Name of the table into which we insert */
395 IdList
*pColumn
, /* List of columns in pTableName to insert into */
396 Select
*pSelect
, /* A SELECT statement that supplies values */
397 u8 orconf
/* The conflict algorithm (OE_Abort, OE_Replace, etc.) */
399 TriggerStep
*pTriggerStep
;
401 assert(pSelect
!= 0 || db
->mallocFailed
);
403 pTriggerStep
= triggerStepAllocate(db
, TK_INSERT
, pTableName
);
405 pTriggerStep
->pSelect
= sqlite3SelectDup(db
, pSelect
, EXPRDUP_REDUCE
);
406 pTriggerStep
->pIdList
= pColumn
;
407 pTriggerStep
->orconf
= orconf
;
409 sqlite3IdListDelete(db
, pColumn
);
411 sqlite3SelectDelete(db
, pSelect
);
417 ** Construct a trigger step that implements an UPDATE statement and return
418 ** a pointer to that trigger step. The parser calls this routine when it
419 ** sees an UPDATE statement inside the body of a CREATE TRIGGER.
421 TriggerStep
*sqlite3TriggerUpdateStep(
422 sqlite3
*db
, /* The database connection */
423 Token
*pTableName
, /* Name of the table to be updated */
424 ExprList
*pEList
, /* The SET clause: list of column and new values */
425 Expr
*pWhere
, /* The WHERE clause */
426 u8 orconf
/* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */
428 TriggerStep
*pTriggerStep
;
430 pTriggerStep
= triggerStepAllocate(db
, TK_UPDATE
, pTableName
);
432 pTriggerStep
->pExprList
= sqlite3ExprListDup(db
, pEList
, EXPRDUP_REDUCE
);
433 pTriggerStep
->pWhere
= sqlite3ExprDup(db
, pWhere
, EXPRDUP_REDUCE
);
434 pTriggerStep
->orconf
= orconf
;
436 sqlite3ExprListDelete(db
, pEList
);
437 sqlite3ExprDelete(db
, pWhere
);
442 ** Construct a trigger step that implements a DELETE statement and return
443 ** a pointer to that trigger step. The parser calls this routine when it
444 ** sees a DELETE statement inside the body of a CREATE TRIGGER.
446 TriggerStep
*sqlite3TriggerDeleteStep(
447 sqlite3
*db
, /* Database connection */
448 Token
*pTableName
, /* The table from which rows are deleted */
449 Expr
*pWhere
/* The WHERE clause */
451 TriggerStep
*pTriggerStep
;
453 pTriggerStep
= triggerStepAllocate(db
, TK_DELETE
, pTableName
);
455 pTriggerStep
->pWhere
= sqlite3ExprDup(db
, pWhere
, EXPRDUP_REDUCE
);
456 pTriggerStep
->orconf
= OE_Default
;
458 sqlite3ExprDelete(db
, pWhere
);
463 ** Recursively delete a Trigger structure
465 void sqlite3DeleteTrigger(sqlite3
*db
, Trigger
*pTrigger
){
466 if( pTrigger
==0 ) return;
467 sqlite3DeleteTriggerStep(db
, pTrigger
->step_list
);
468 sqlite3DbFree(db
, pTrigger
->zName
);
469 sqlite3DbFree(db
, pTrigger
->table
);
470 sqlite3ExprDelete(db
, pTrigger
->pWhen
);
471 sqlite3IdListDelete(db
, pTrigger
->pColumns
);
472 sqlite3DbFree(db
, pTrigger
);
476 ** This function is called to drop a trigger from the database schema.
478 ** This may be called directly from the parser and therefore identifies
479 ** the trigger by name. The sqlite3DropTriggerPtr() routine does the
480 ** same job as this routine except it takes a pointer to the trigger
481 ** instead of the trigger name.
483 void sqlite3DropTrigger(Parse
*pParse
, SrcList
*pName
, int noErr
){
484 Trigger
*pTrigger
= 0;
488 sqlite3
*db
= pParse
->db
;
490 if( db
->mallocFailed
) goto drop_trigger_cleanup
;
491 if( SQLITE_OK
!=sqlite3ReadSchema(pParse
) ){
492 goto drop_trigger_cleanup
;
495 assert( pName
->nSrc
==1 );
496 zDb
= pName
->a
[0].zDatabase
;
497 zName
= pName
->a
[0].zName
;
498 assert( zDb
!=0 || sqlite3BtreeHoldsAllMutexes(db
) );
499 for(i
=OMIT_TEMPDB
; i
<db
->nDb
; i
++){
500 int j
= (i
<2) ? i
^1 : i
; /* Search TEMP before MAIN */
501 if( zDb
&& sqlite3StrICmp(db
->aDb
[j
].zDbSName
, zDb
) ) continue;
502 assert( sqlite3SchemaMutexHeld(db
, j
, 0) );
503 pTrigger
= sqlite3HashFind(&(db
->aDb
[j
].pSchema
->trigHash
), zName
);
504 if( pTrigger
) break;
508 sqlite3ErrorMsg(pParse
, "no such trigger: %S", pName
, 0);
510 sqlite3CodeVerifyNamedSchema(pParse
, zDb
);
512 pParse
->checkSchema
= 1;
513 goto drop_trigger_cleanup
;
515 sqlite3DropTriggerPtr(pParse
, pTrigger
);
517 drop_trigger_cleanup
:
518 sqlite3SrcListDelete(db
, pName
);
522 ** Return a pointer to the Table structure for the table that a trigger
525 static Table
*tableOfTrigger(Trigger
*pTrigger
){
526 return sqlite3HashFind(&pTrigger
->pTabSchema
->tblHash
, pTrigger
->table
);
531 ** Drop a trigger given a pointer to that trigger.
533 void sqlite3DropTriggerPtr(Parse
*pParse
, Trigger
*pTrigger
){
536 sqlite3
*db
= pParse
->db
;
539 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTrigger
->pSchema
);
540 assert( iDb
>=0 && iDb
<db
->nDb
);
541 pTable
= tableOfTrigger(pTrigger
);
543 assert( pTable
->pSchema
==pTrigger
->pSchema
|| iDb
==1 );
544 #ifndef SQLITE_OMIT_AUTHORIZATION
546 int code
= SQLITE_DROP_TRIGGER
;
547 const char *zDb
= db
->aDb
[iDb
].zDbSName
;
548 const char *zTab
= SCHEMA_TABLE(iDb
);
549 if( iDb
==1 ) code
= SQLITE_DROP_TEMP_TRIGGER
;
550 if( sqlite3AuthCheck(pParse
, code
, pTrigger
->zName
, pTable
->zName
, zDb
) ||
551 sqlite3AuthCheck(pParse
, SQLITE_DELETE
, zTab
, 0, zDb
) ){
557 /* Generate code to destroy the database record of the trigger.
560 if( (v
= sqlite3GetVdbe(pParse
))!=0 ){
561 sqlite3NestedParse(pParse
,
562 "DELETE FROM %Q.%s WHERE name=%Q AND type='trigger'",
563 db
->aDb
[iDb
].zDbSName
, MASTER_NAME
, pTrigger
->zName
565 sqlite3ChangeCookie(pParse
, iDb
);
566 sqlite3VdbeAddOp4(v
, OP_DropTrigger
, iDb
, 0, 0, pTrigger
->zName
, 0);
571 ** Remove a trigger from the hash tables of the sqlite* pointer.
573 void sqlite3UnlinkAndDeleteTrigger(sqlite3
*db
, int iDb
, const char *zName
){
577 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
578 pHash
= &(db
->aDb
[iDb
].pSchema
->trigHash
);
579 pTrigger
= sqlite3HashInsert(pHash
, zName
, 0);
580 if( ALWAYS(pTrigger
) ){
581 if( pTrigger
->pSchema
==pTrigger
->pTabSchema
){
582 Table
*pTab
= tableOfTrigger(pTrigger
);
584 for(pp
=&pTab
->pTrigger
; *pp
!=pTrigger
; pp
=&((*pp
)->pNext
));
587 sqlite3DeleteTrigger(db
, pTrigger
);
588 db
->mDbFlags
|= DBFLAG_SchemaChange
;
593 ** pEList is the SET clause of an UPDATE statement. Each entry
594 ** in pEList is of the format <id>=<expr>. If any of the entries
595 ** in pEList have an <id> which matches an identifier in pIdList,
596 ** then return TRUE. If pIdList==NULL, then it is considered a
597 ** wildcard that matches anything. Likewise if pEList==NULL then
598 ** it matches anything so always return true. Return false only
599 ** if there is no match.
601 static int checkColumnOverlap(IdList
*pIdList
, ExprList
*pEList
){
603 if( pIdList
==0 || NEVER(pEList
==0) ) return 1;
604 for(e
=0; e
<pEList
->nExpr
; e
++){
605 if( sqlite3IdListIndex(pIdList
, pEList
->a
[e
].zName
)>=0 ) return 1;
611 ** Return a list of all triggers on table pTab if there exists at least
612 ** one trigger that must be fired when an operation of type 'op' is
613 ** performed on the table, and, if that operation is an UPDATE, if at
614 ** least one of the columns in pChanges is being modified.
616 Trigger
*sqlite3TriggersExist(
617 Parse
*pParse
, /* Parse context */
618 Table
*pTab
, /* The table the contains the triggers */
619 int op
, /* one of TK_DELETE, TK_INSERT, TK_UPDATE */
620 ExprList
*pChanges
, /* Columns that change in an UPDATE statement */
621 int *pMask
/* OUT: Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
627 if( (pParse
->db
->flags
& SQLITE_EnableTrigger
)!=0 ){
628 pList
= sqlite3TriggerList(pParse
, pTab
);
630 assert( pList
==0 || IsVirtual(pTab
)==0 );
631 for(p
=pList
; p
; p
=p
->pNext
){
632 if( p
->op
==op
&& checkColumnOverlap(p
->pColumns
, pChanges
) ){
639 return (mask
? pList
: 0);
643 ** Convert the pStep->zTarget string into a SrcList and return a pointer
646 ** This routine adds a specific database name, if needed, to the target when
647 ** forming the SrcList. This prevents a trigger in one database from
648 ** referring to a target in another database. An exception is when the
649 ** trigger is in TEMP in which case it can refer to any other database it
652 static SrcList
*targetSrcList(
653 Parse
*pParse
, /* The parsing context */
654 TriggerStep
*pStep
/* The trigger containing the target token */
656 sqlite3
*db
= pParse
->db
;
657 int iDb
; /* Index of the database to use */
658 SrcList
*pSrc
; /* SrcList to be returned */
660 pSrc
= sqlite3SrcListAppend(db
, 0, 0, 0);
662 assert( pSrc
->nSrc
>0 );
663 pSrc
->a
[pSrc
->nSrc
-1].zName
= sqlite3DbStrDup(db
, pStep
->zTarget
);
664 iDb
= sqlite3SchemaToIndex(db
, pStep
->pTrig
->pSchema
);
665 if( iDb
==0 || iDb
>=2 ){
667 assert( iDb
<db
->nDb
);
668 zDb
= db
->aDb
[iDb
].zDbSName
;
669 pSrc
->a
[pSrc
->nSrc
-1].zDatabase
= sqlite3DbStrDup(db
, zDb
);
676 ** Generate VDBE code for the statements inside the body of a single
679 static int codeTriggerProgram(
680 Parse
*pParse
, /* The parser context */
681 TriggerStep
*pStepList
, /* List of statements inside the trigger body */
682 int orconf
/* Conflict algorithm. (OE_Abort, etc) */
685 Vdbe
*v
= pParse
->pVdbe
;
686 sqlite3
*db
= pParse
->db
;
688 assert( pParse
->pTriggerTab
&& pParse
->pToplevel
);
691 for(pStep
=pStepList
; pStep
; pStep
=pStep
->pNext
){
692 /* Figure out the ON CONFLICT policy that will be used for this step
693 ** of the trigger program. If the statement that caused this trigger
694 ** to fire had an explicit ON CONFLICT, then use it. Otherwise, use
695 ** the ON CONFLICT policy that was specified as part of the trigger
696 ** step statement. Example:
698 ** CREATE TRIGGER AFTER INSERT ON t1 BEGIN;
699 ** INSERT OR REPLACE INTO t2 VALUES(new.a, new.b);
702 ** INSERT INTO t1 ... ; -- insert into t2 uses REPLACE policy
703 ** INSERT OR IGNORE INTO t1 ... ; -- insert into t2 uses IGNORE policy
705 pParse
->eOrconf
= (orconf
==OE_Default
)?pStep
->orconf
:(u8
)orconf
;
706 assert( pParse
->okConstFactor
==0 );
710 sqlite3Update(pParse
,
711 targetSrcList(pParse
, pStep
),
712 sqlite3ExprListDup(db
, pStep
->pExprList
, 0),
713 sqlite3ExprDup(db
, pStep
->pWhere
, 0),
719 sqlite3Insert(pParse
,
720 targetSrcList(pParse
, pStep
),
721 sqlite3SelectDup(db
, pStep
->pSelect
, 0),
722 sqlite3IdListDup(db
, pStep
->pIdList
),
728 sqlite3DeleteFrom(pParse
,
729 targetSrcList(pParse
, pStep
),
730 sqlite3ExprDup(db
, pStep
->pWhere
, 0)
734 default: assert( pStep
->op
==TK_SELECT
); {
736 Select
*pSelect
= sqlite3SelectDup(db
, pStep
->pSelect
, 0);
737 sqlite3SelectDestInit(&sDest
, SRT_Discard
, 0);
738 sqlite3Select(pParse
, pSelect
, &sDest
);
739 sqlite3SelectDelete(db
, pSelect
);
743 if( pStep
->op
!=TK_SELECT
){
744 sqlite3VdbeAddOp0(v
, OP_ResetCount
);
751 #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
753 ** This function is used to add VdbeComment() annotations to a VDBE
754 ** program. It is not used in production code, only for debugging.
756 static const char *onErrorText(int onError
){
758 case OE_Abort
: return "abort";
759 case OE_Rollback
: return "rollback";
760 case OE_Fail
: return "fail";
761 case OE_Replace
: return "replace";
762 case OE_Ignore
: return "ignore";
763 case OE_Default
: return "default";
770 ** Parse context structure pFrom has just been used to create a sub-vdbe
771 ** (trigger program). If an error has occurred, transfer error information
772 ** from pFrom to pTo.
774 static void transferParseError(Parse
*pTo
, Parse
*pFrom
){
775 assert( pFrom
->zErrMsg
==0 || pFrom
->nErr
);
776 assert( pTo
->zErrMsg
==0 || pTo
->nErr
);
778 pTo
->zErrMsg
= pFrom
->zErrMsg
;
779 pTo
->nErr
= pFrom
->nErr
;
782 sqlite3DbFree(pFrom
->db
, pFrom
->zErrMsg
);
787 ** Create and populate a new TriggerPrg object with a sub-program
788 ** implementing trigger pTrigger with ON CONFLICT policy orconf.
790 static TriggerPrg
*codeRowTrigger(
791 Parse
*pParse
, /* Current parse context */
792 Trigger
*pTrigger
, /* Trigger to code */
793 Table
*pTab
, /* The table pTrigger is attached to */
794 int orconf
/* ON CONFLICT policy to code trigger program with */
796 Parse
*pTop
= sqlite3ParseToplevel(pParse
);
797 sqlite3
*db
= pParse
->db
; /* Database handle */
798 TriggerPrg
*pPrg
; /* Value to return */
799 Expr
*pWhen
= 0; /* Duplicate of trigger WHEN expression */
800 Vdbe
*v
; /* Temporary VM */
801 NameContext sNC
; /* Name context for sub-vdbe */
802 SubProgram
*pProgram
= 0; /* Sub-vdbe for trigger program */
803 Parse
*pSubParse
; /* Parse context for sub-vdbe */
804 int iEndTrigger
= 0; /* Label to jump to if WHEN is false */
806 assert( pTrigger
->zName
==0 || pTab
==tableOfTrigger(pTrigger
) );
807 assert( pTop
->pVdbe
);
809 /* Allocate the TriggerPrg and SubProgram objects. To ensure that they
810 ** are freed if an error occurs, link them into the Parse.pTriggerPrg
811 ** list of the top-level Parse object sooner rather than later. */
812 pPrg
= sqlite3DbMallocZero(db
, sizeof(TriggerPrg
));
813 if( !pPrg
) return 0;
814 pPrg
->pNext
= pTop
->pTriggerPrg
;
815 pTop
->pTriggerPrg
= pPrg
;
816 pPrg
->pProgram
= pProgram
= sqlite3DbMallocZero(db
, sizeof(SubProgram
));
817 if( !pProgram
) return 0;
818 sqlite3VdbeLinkSubProgram(pTop
->pVdbe
, pProgram
);
819 pPrg
->pTrigger
= pTrigger
;
820 pPrg
->orconf
= orconf
;
821 pPrg
->aColmask
[0] = 0xffffffff;
822 pPrg
->aColmask
[1] = 0xffffffff;
824 /* Allocate and populate a new Parse context to use for coding the
825 ** trigger sub-program. */
826 pSubParse
= sqlite3StackAllocZero(db
, sizeof(Parse
));
827 if( !pSubParse
) return 0;
828 memset(&sNC
, 0, sizeof(sNC
));
829 sNC
.pParse
= pSubParse
;
831 pSubParse
->pTriggerTab
= pTab
;
832 pSubParse
->pToplevel
= pTop
;
833 pSubParse
->zAuthContext
= pTrigger
->zName
;
834 pSubParse
->eTriggerOp
= pTrigger
->op
;
835 pSubParse
->nQueryLoop
= pParse
->nQueryLoop
;
837 v
= sqlite3GetVdbe(pSubParse
);
839 VdbeComment((v
, "Start: %s.%s (%s %s%s%s ON %s)",
840 pTrigger
->zName
, onErrorText(orconf
),
841 (pTrigger
->tr_tm
==TRIGGER_BEFORE
? "BEFORE" : "AFTER"),
842 (pTrigger
->op
==TK_UPDATE
? "UPDATE" : ""),
843 (pTrigger
->op
==TK_INSERT
? "INSERT" : ""),
844 (pTrigger
->op
==TK_DELETE
? "DELETE" : ""),
847 #ifndef SQLITE_OMIT_TRACE
848 sqlite3VdbeChangeP4(v
, -1,
849 sqlite3MPrintf(db
, "-- TRIGGER %s", pTrigger
->zName
), P4_DYNAMIC
853 /* If one was specified, code the WHEN clause. If it evaluates to false
854 ** (or NULL) the sub-vdbe is immediately halted by jumping to the
855 ** OP_Halt inserted at the end of the program. */
856 if( pTrigger
->pWhen
){
857 pWhen
= sqlite3ExprDup(db
, pTrigger
->pWhen
, 0);
858 if( SQLITE_OK
==sqlite3ResolveExprNames(&sNC
, pWhen
)
859 && db
->mallocFailed
==0
861 iEndTrigger
= sqlite3VdbeMakeLabel(v
);
862 sqlite3ExprIfFalse(pSubParse
, pWhen
, iEndTrigger
, SQLITE_JUMPIFNULL
);
864 sqlite3ExprDelete(db
, pWhen
);
867 /* Code the trigger program into the sub-vdbe. */
868 codeTriggerProgram(pSubParse
, pTrigger
->step_list
, orconf
);
870 /* Insert an OP_Halt at the end of the sub-program. */
872 sqlite3VdbeResolveLabel(v
, iEndTrigger
);
874 sqlite3VdbeAddOp0(v
, OP_Halt
);
875 VdbeComment((v
, "End: %s.%s", pTrigger
->zName
, onErrorText(orconf
)));
877 transferParseError(pParse
, pSubParse
);
878 if( db
->mallocFailed
==0 ){
879 pProgram
->aOp
= sqlite3VdbeTakeOpArray(v
, &pProgram
->nOp
, &pTop
->nMaxArg
);
881 pProgram
->nMem
= pSubParse
->nMem
;
882 pProgram
->nCsr
= pSubParse
->nTab
;
883 pProgram
->token
= (void *)pTrigger
;
884 pPrg
->aColmask
[0] = pSubParse
->oldmask
;
885 pPrg
->aColmask
[1] = pSubParse
->newmask
;
886 sqlite3VdbeDelete(v
);
889 assert( !pSubParse
->pAinc
&& !pSubParse
->pZombieTab
);
890 assert( !pSubParse
->pTriggerPrg
&& !pSubParse
->nMaxArg
);
891 sqlite3ParserReset(pSubParse
);
892 sqlite3StackFree(db
, pSubParse
);
898 ** Return a pointer to a TriggerPrg object containing the sub-program for
899 ** trigger pTrigger with default ON CONFLICT algorithm orconf. If no such
900 ** TriggerPrg object exists, a new object is allocated and populated before
903 static TriggerPrg
*getRowTrigger(
904 Parse
*pParse
, /* Current parse context */
905 Trigger
*pTrigger
, /* Trigger to code */
906 Table
*pTab
, /* The table trigger pTrigger is attached to */
907 int orconf
/* ON CONFLICT algorithm. */
909 Parse
*pRoot
= sqlite3ParseToplevel(pParse
);
912 assert( pTrigger
->zName
==0 || pTab
==tableOfTrigger(pTrigger
) );
914 /* It may be that this trigger has already been coded (or is in the
915 ** process of being coded). If this is the case, then an entry with
916 ** a matching TriggerPrg.pTrigger field will be present somewhere
917 ** in the Parse.pTriggerPrg list. Search for such an entry. */
918 for(pPrg
=pRoot
->pTriggerPrg
;
919 pPrg
&& (pPrg
->pTrigger
!=pTrigger
|| pPrg
->orconf
!=orconf
);
923 /* If an existing TriggerPrg could not be located, create a new one. */
925 pPrg
= codeRowTrigger(pParse
, pTrigger
, pTab
, orconf
);
932 ** Generate code for the trigger program associated with trigger p on
933 ** table pTab. The reg, orconf and ignoreJump parameters passed to this
934 ** function are the same as those described in the header function for
935 ** sqlite3CodeRowTrigger()
937 void sqlite3CodeRowTriggerDirect(
938 Parse
*pParse
, /* Parse context */
939 Trigger
*p
, /* Trigger to code */
940 Table
*pTab
, /* The table to code triggers from */
941 int reg
, /* Reg array containing OLD.* and NEW.* values */
942 int orconf
, /* ON CONFLICT policy */
943 int ignoreJump
/* Instruction to jump to for RAISE(IGNORE) */
945 Vdbe
*v
= sqlite3GetVdbe(pParse
); /* Main VM */
947 pPrg
= getRowTrigger(pParse
, p
, pTab
, orconf
);
948 assert( pPrg
|| pParse
->nErr
|| pParse
->db
->mallocFailed
);
950 /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program
951 ** is a pointer to the sub-vdbe containing the trigger program. */
953 int bRecursive
= (p
->zName
&& 0==(pParse
->db
->flags
&SQLITE_RecTriggers
));
955 sqlite3VdbeAddOp4(v
, OP_Program
, reg
, ignoreJump
, ++pParse
->nMem
,
956 (const char *)pPrg
->pProgram
, P4_SUBPROGRAM
);
958 (v
, "Call: %s.%s", (p
->zName
?p
->zName
:"fkey"), onErrorText(orconf
)));
960 /* Set the P5 operand of the OP_Program instruction to non-zero if
961 ** recursive invocation of this trigger program is disallowed. Recursive
962 ** invocation is disallowed if (a) the sub-program is really a trigger,
963 ** not a foreign key action, and (b) the flag to enable recursive triggers
965 sqlite3VdbeChangeP5(v
, (u8
)bRecursive
);
970 ** This is called to code the required FOR EACH ROW triggers for an operation
971 ** on table pTab. The operation to code triggers for (INSERT, UPDATE or DELETE)
972 ** is given by the op parameter. The tr_tm parameter determines whether the
973 ** BEFORE or AFTER triggers are coded. If the operation is an UPDATE, then
974 ** parameter pChanges is passed the list of columns being modified.
976 ** If there are no triggers that fire at the specified time for the specified
977 ** operation on pTab, this function is a no-op.
979 ** The reg argument is the address of the first in an array of registers
980 ** that contain the values substituted for the new.* and old.* references
981 ** in the trigger program. If N is the number of columns in table pTab
982 ** (a copy of pTab->nCol), then registers are populated as follows:
985 ** ------------------------------------------------------
987 ** reg+1 OLD.* value of left-most column of pTab
989 ** reg+N OLD.* value of right-most column of pTab
991 ** reg+N+2 OLD.* value of left-most column of pTab
993 ** reg+N+N+1 NEW.* value of right-most column of pTab
995 ** For ON DELETE triggers, the registers containing the NEW.* values will
996 ** never be accessed by the trigger program, so they are not allocated or
997 ** populated by the caller (there is no data to populate them with anyway).
998 ** Similarly, for ON INSERT triggers the values stored in the OLD.* registers
999 ** are never accessed, and so are not allocated by the caller. So, for an
1000 ** ON INSERT trigger, the value passed to this function as parameter reg
1001 ** is not a readable register, although registers (reg+N) through
1004 ** Parameter orconf is the default conflict resolution algorithm for the
1005 ** trigger program to use (REPLACE, IGNORE etc.). Parameter ignoreJump
1006 ** is the instruction that control should jump to if a trigger program
1007 ** raises an IGNORE exception.
1009 void sqlite3CodeRowTrigger(
1010 Parse
*pParse
, /* Parse context */
1011 Trigger
*pTrigger
, /* List of triggers on table pTab */
1012 int op
, /* One of TK_UPDATE, TK_INSERT, TK_DELETE */
1013 ExprList
*pChanges
, /* Changes list for any UPDATE OF triggers */
1014 int tr_tm
, /* One of TRIGGER_BEFORE, TRIGGER_AFTER */
1015 Table
*pTab
, /* The table to code triggers from */
1016 int reg
, /* The first in an array of registers (see above) */
1017 int orconf
, /* ON CONFLICT policy */
1018 int ignoreJump
/* Instruction to jump to for RAISE(IGNORE) */
1020 Trigger
*p
; /* Used to iterate through pTrigger list */
1022 assert( op
==TK_UPDATE
|| op
==TK_INSERT
|| op
==TK_DELETE
);
1023 assert( tr_tm
==TRIGGER_BEFORE
|| tr_tm
==TRIGGER_AFTER
);
1024 assert( (op
==TK_UPDATE
)==(pChanges
!=0) );
1026 for(p
=pTrigger
; p
; p
=p
->pNext
){
1028 /* Sanity checking: The schema for the trigger and for the table are
1029 ** always defined. The trigger must be in the same schema as the table
1030 ** or else it must be a TEMP trigger. */
1031 assert( p
->pSchema
!=0 );
1032 assert( p
->pTabSchema
!=0 );
1033 assert( p
->pSchema
==p
->pTabSchema
1034 || p
->pSchema
==pParse
->db
->aDb
[1].pSchema
);
1036 /* Determine whether we should code this trigger */
1039 && checkColumnOverlap(p
->pColumns
, pChanges
)
1041 sqlite3CodeRowTriggerDirect(pParse
, p
, pTab
, reg
, orconf
, ignoreJump
);
1047 ** Triggers may access values stored in the old.* or new.* pseudo-table.
1048 ** This function returns a 32-bit bitmask indicating which columns of the
1049 ** old.* or new.* tables actually are used by triggers. This information
1050 ** may be used by the caller, for example, to avoid having to load the entire
1051 ** old.* record into memory when executing an UPDATE or DELETE command.
1053 ** Bit 0 of the returned mask is set if the left-most column of the
1054 ** table may be accessed using an [old|new].<col> reference. Bit 1 is set if
1055 ** the second leftmost column value is required, and so on. If there
1056 ** are more than 32 columns in the table, and at least one of the columns
1057 ** with an index greater than 32 may be accessed, 0xffffffff is returned.
1059 ** It is not possible to determine if the old.rowid or new.rowid column is
1060 ** accessed by triggers. The caller must always assume that it is.
1062 ** Parameter isNew must be either 1 or 0. If it is 0, then the mask returned
1063 ** applies to the old.* table. If 1, the new.* table.
1065 ** Parameter tr_tm must be a mask with one or both of the TRIGGER_BEFORE
1066 ** and TRIGGER_AFTER bits set. Values accessed by BEFORE triggers are only
1067 ** included in the returned mask if the TRIGGER_BEFORE bit is set in the
1068 ** tr_tm parameter. Similarly, values accessed by AFTER triggers are only
1069 ** included in the returned mask if the TRIGGER_AFTER bit is set in tr_tm.
1071 u32
sqlite3TriggerColmask(
1072 Parse
*pParse
, /* Parse context */
1073 Trigger
*pTrigger
, /* List of triggers on table pTab */
1074 ExprList
*pChanges
, /* Changes list for any UPDATE OF triggers */
1075 int isNew
, /* 1 for new.* ref mask, 0 for old.* ref mask */
1076 int tr_tm
, /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
1077 Table
*pTab
, /* The table to code triggers from */
1078 int orconf
/* Default ON CONFLICT policy for trigger steps */
1080 const int op
= pChanges
? TK_UPDATE
: TK_DELETE
;
1084 assert( isNew
==1 || isNew
==0 );
1085 for(p
=pTrigger
; p
; p
=p
->pNext
){
1086 if( p
->op
==op
&& (tr_tm
&p
->tr_tm
)
1087 && checkColumnOverlap(p
->pColumns
,pChanges
)
1090 pPrg
= getRowTrigger(pParse
, p
, pTab
, orconf
);
1092 mask
|= pPrg
->aColmask
[isNew
];
1100 #endif /* !defined(SQLITE_OMIT_TRIGGER) */