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
);
28 sqlite3UpsertDelete(db
, pTmp
->pUpsert
);
29 sqlite3DbFree(db
, pTmp
->zSpan
);
31 sqlite3DbFree(db
, pTmp
);
36 ** Given table pTab, return a list of all the triggers attached to
37 ** the table. The list is connected by Trigger.pNext pointers.
39 ** All of the triggers on pTab that are in the same database as pTab
40 ** are already attached to pTab->pTrigger. But there might be additional
41 ** triggers on pTab in the TEMP schema. This routine prepends all
42 ** TEMP triggers on pTab to the beginning of the pTab->pTrigger list
43 ** and returns the combined list.
45 ** To state it another way: This routine returns a list of all triggers
46 ** that fire off of pTab. The list will include any TEMP triggers on
47 ** pTab as well as the triggers lised in pTab->pTrigger.
49 Trigger
*sqlite3TriggerList(Parse
*pParse
, Table
*pTab
){
50 Schema
* const pTmpSchema
= pParse
->db
->aDb
[1].pSchema
;
51 Trigger
*pList
= 0; /* List of triggers to return */
53 if( pParse
->disableTriggers
){
57 if( pTmpSchema
!=pTab
->pSchema
){
59 assert( sqlite3SchemaMutexHeld(pParse
->db
, 0, pTmpSchema
) );
60 for(p
=sqliteHashFirst(&pTmpSchema
->trigHash
); p
; p
=sqliteHashNext(p
)){
61 Trigger
*pTrig
= (Trigger
*)sqliteHashData(p
);
62 if( pTrig
->pTabSchema
==pTab
->pSchema
63 && 0==sqlite3StrICmp(pTrig
->table
, pTab
->zName
)
65 pTrig
->pNext
= (pList
? pList
: pTab
->pTrigger
);
71 return (pList
? pList
: pTab
->pTrigger
);
75 ** This is called by the parser when it sees a CREATE TRIGGER statement
76 ** up to the point of the BEGIN before the trigger actions. A Trigger
77 ** structure is generated based on the information available and stored
78 ** in pParse->pNewTrigger. After the trigger actions have been parsed, the
79 ** sqlite3FinishTrigger() function is called to complete the trigger
80 ** construction process.
82 void sqlite3BeginTrigger(
83 Parse
*pParse
, /* The parse context of the CREATE TRIGGER statement */
84 Token
*pName1
, /* The name of the trigger */
85 Token
*pName2
, /* The name of the trigger */
86 int tr_tm
, /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD */
87 int op
, /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
88 IdList
*pColumns
, /* column list if this is an UPDATE OF trigger */
89 SrcList
*pTableName
,/* The name of the table/view the trigger applies to */
90 Expr
*pWhen
, /* WHEN clause */
91 int isTemp
, /* True if the TEMPORARY keyword is present */
92 int noErr
/* Suppress errors if the trigger already exists */
94 Trigger
*pTrigger
= 0; /* The new trigger */
95 Table
*pTab
; /* Table that the trigger fires off of */
96 char *zName
= 0; /* Name of the trigger */
97 sqlite3
*db
= pParse
->db
; /* The database connection */
98 int iDb
; /* The database to store the trigger in */
99 Token
*pName
; /* The unqualified db name */
100 DbFixer sFix
; /* State vector for the DB fixer */
102 assert( pName1
!=0 ); /* pName1->z might be NULL, but not pName1 itself */
104 assert( op
==TK_INSERT
|| op
==TK_UPDATE
|| op
==TK_DELETE
);
105 assert( op
>0 && op
<0xff );
107 /* If TEMP was specified, then the trigger name may not be qualified. */
109 sqlite3ErrorMsg(pParse
, "temporary trigger may not have qualified name");
110 goto trigger_cleanup
;
115 /* Figure out the db that the trigger will be created in */
116 iDb
= sqlite3TwoPartName(pParse
, pName1
, pName2
, &pName
);
118 goto trigger_cleanup
;
121 if( !pTableName
|| db
->mallocFailed
){
122 goto trigger_cleanup
;
125 /* A long-standing parser bug is that this syntax was allowed:
127 ** CREATE TRIGGER attached.demo AFTER INSERT ON attached.tab ....
130 ** To maintain backwards compatibility, ignore the database
131 ** name on pTableName if we are reparsing out of SQLITE_MASTER.
133 if( db
->init
.busy
&& iDb
!=1 ){
134 sqlite3DbFree(db
, pTableName
->a
[0].zDatabase
);
135 pTableName
->a
[0].zDatabase
= 0;
138 /* If the trigger name was unqualified, and the table is a temp table,
139 ** then set iDb to 1 to create the trigger in the temporary database.
140 ** If sqlite3SrcListLookup() returns 0, indicating the table does not
141 ** exist, the error is caught by the block below.
143 pTab
= sqlite3SrcListLookup(pParse
, pTableName
);
144 if( db
->init
.busy
==0 && pName2
->n
==0 && pTab
145 && pTab
->pSchema
==db
->aDb
[1].pSchema
){
149 /* Ensure the table name matches database name and that the table exists */
150 if( db
->mallocFailed
) goto trigger_cleanup
;
151 assert( pTableName
->nSrc
==1 );
152 sqlite3FixInit(&sFix
, pParse
, iDb
, "trigger", pName
);
153 if( sqlite3FixSrcList(&sFix
, pTableName
) ){
154 goto trigger_cleanup
;
156 pTab
= sqlite3SrcListLookup(pParse
, pTableName
);
158 /* The table does not exist. */
159 if( db
->init
.iDb
==1 ){
161 ** Normally, whenever a table is dropped, all associated triggers are
162 ** dropped too. But if a TEMP trigger is created on a non-TEMP table
163 ** and the table is dropped by a different database connection, the
164 ** trigger is not visible to the database connection that does the
165 ** drop so the trigger cannot be dropped. This results in an
166 ** "orphaned trigger" - a trigger whose associated table is missing.
168 db
->init
.orphanTrigger
= 1;
170 goto trigger_cleanup
;
172 if( IsVirtual(pTab
) ){
173 sqlite3ErrorMsg(pParse
, "cannot create triggers on virtual tables");
174 goto trigger_cleanup
;
177 /* Check that the trigger name is not reserved and that no trigger of the
178 ** specified name exists */
179 zName
= sqlite3NameFromToken(db
, pName
);
180 if( !zName
|| SQLITE_OK
!=sqlite3CheckObjectName(pParse
, zName
) ){
181 goto trigger_cleanup
;
183 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
184 if( sqlite3HashFind(&(db
->aDb
[iDb
].pSchema
->trigHash
),zName
) ){
186 sqlite3ErrorMsg(pParse
, "trigger %T already exists", pName
);
188 assert( !db
->init
.busy
);
189 sqlite3CodeVerifySchema(pParse
, iDb
);
191 goto trigger_cleanup
;
194 /* Do not create a trigger on a system table */
195 if( sqlite3StrNICmp(pTab
->zName
, "sqlite_", 7)==0 ){
196 sqlite3ErrorMsg(pParse
, "cannot create trigger on system table");
197 goto trigger_cleanup
;
200 /* INSTEAD of triggers are only for views and views only support INSTEAD
203 if( pTab
->pSelect
&& tr_tm
!=TK_INSTEAD
){
204 sqlite3ErrorMsg(pParse
, "cannot create %s trigger on view: %S",
205 (tr_tm
== TK_BEFORE
)?"BEFORE":"AFTER", pTableName
, 0);
206 goto trigger_cleanup
;
208 if( !pTab
->pSelect
&& tr_tm
==TK_INSTEAD
){
209 sqlite3ErrorMsg(pParse
, "cannot create INSTEAD OF"
210 " trigger on table: %S", pTableName
, 0);
211 goto trigger_cleanup
;
214 #ifndef SQLITE_OMIT_AUTHORIZATION
216 int iTabDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
217 int code
= SQLITE_CREATE_TRIGGER
;
218 const char *zDb
= db
->aDb
[iTabDb
].zDbSName
;
219 const char *zDbTrig
= isTemp
? db
->aDb
[1].zDbSName
: zDb
;
220 if( iTabDb
==1 || isTemp
) code
= SQLITE_CREATE_TEMP_TRIGGER
;
221 if( sqlite3AuthCheck(pParse
, code
, zName
, pTab
->zName
, zDbTrig
) ){
222 goto trigger_cleanup
;
224 if( sqlite3AuthCheck(pParse
, SQLITE_INSERT
, SCHEMA_TABLE(iTabDb
),0,zDb
)){
225 goto trigger_cleanup
;
230 /* INSTEAD OF triggers can only appear on views and BEFORE triggers
231 ** cannot appear on views. So we might as well translate every
232 ** INSTEAD OF trigger into a BEFORE trigger. It simplifies code
235 if (tr_tm
== TK_INSTEAD
){
239 /* Build the Trigger object */
240 pTrigger
= (Trigger
*)sqlite3DbMallocZero(db
, sizeof(Trigger
));
241 if( pTrigger
==0 ) goto trigger_cleanup
;
242 pTrigger
->zName
= zName
;
244 pTrigger
->table
= sqlite3DbStrDup(db
, pTableName
->a
[0].zName
);
245 pTrigger
->pSchema
= db
->aDb
[iDb
].pSchema
;
246 pTrigger
->pTabSchema
= pTab
->pSchema
;
247 pTrigger
->op
= (u8
)op
;
248 pTrigger
->tr_tm
= tr_tm
==TK_BEFORE
? TRIGGER_BEFORE
: TRIGGER_AFTER
;
249 pTrigger
->pWhen
= sqlite3ExprDup(db
, pWhen
, EXPRDUP_REDUCE
);
250 pTrigger
->pColumns
= sqlite3IdListDup(db
, pColumns
);
251 assert( pParse
->pNewTrigger
==0 );
252 pParse
->pNewTrigger
= pTrigger
;
255 sqlite3DbFree(db
, zName
);
256 sqlite3SrcListDelete(db
, pTableName
);
257 sqlite3IdListDelete(db
, pColumns
);
258 sqlite3ExprDelete(db
, pWhen
);
259 if( !pParse
->pNewTrigger
){
260 sqlite3DeleteTrigger(db
, pTrigger
);
262 assert( pParse
->pNewTrigger
==pTrigger
);
267 ** This routine is called after all of the trigger actions have been parsed
268 ** in order to complete the process of building the trigger.
270 void sqlite3FinishTrigger(
271 Parse
*pParse
, /* Parser context */
272 TriggerStep
*pStepList
, /* The triggered program */
273 Token
*pAll
/* Token that describes the complete CREATE TRIGGER */
275 Trigger
*pTrig
= pParse
->pNewTrigger
; /* Trigger being finished */
276 char *zName
; /* Name of trigger */
277 sqlite3
*db
= pParse
->db
; /* The database */
278 DbFixer sFix
; /* Fixer object */
279 int iDb
; /* Database containing the trigger */
280 Token nameToken
; /* Trigger name for error reporting */
282 pParse
->pNewTrigger
= 0;
283 if( NEVER(pParse
->nErr
) || !pTrig
) goto triggerfinish_cleanup
;
284 zName
= pTrig
->zName
;
285 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTrig
->pSchema
);
286 pTrig
->step_list
= pStepList
;
288 pStepList
->pTrig
= pTrig
;
289 pStepList
= pStepList
->pNext
;
291 sqlite3TokenInit(&nameToken
, pTrig
->zName
);
292 sqlite3FixInit(&sFix
, pParse
, iDb
, "trigger", &nameToken
);
293 if( sqlite3FixTriggerStep(&sFix
, pTrig
->step_list
)
294 || sqlite3FixExpr(&sFix
, pTrig
->pWhen
)
296 goto triggerfinish_cleanup
;
299 /* if we are not initializing,
300 ** build the sqlite_master entry
302 if( !db
->init
.busy
){
306 /* Make an entry in the sqlite_master table */
307 v
= sqlite3GetVdbe(pParse
);
308 if( v
==0 ) goto triggerfinish_cleanup
;
309 sqlite3BeginWriteOperation(pParse
, 0, iDb
);
310 z
= sqlite3DbStrNDup(db
, (char*)pAll
->z
, pAll
->n
);
312 sqlite3NestedParse(pParse
,
313 "INSERT INTO %Q.%s VALUES('trigger',%Q,%Q,0,'CREATE TRIGGER %q')",
314 db
->aDb
[iDb
].zDbSName
, MASTER_NAME
, zName
,
316 sqlite3DbFree(db
, z
);
317 sqlite3ChangeCookie(pParse
, iDb
);
318 sqlite3VdbeAddParseSchemaOp(v
, iDb
,
319 sqlite3MPrintf(db
, "type='trigger' AND name='%q'", zName
));
323 Trigger
*pLink
= pTrig
;
324 Hash
*pHash
= &db
->aDb
[iDb
].pSchema
->trigHash
;
325 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
326 pTrig
= sqlite3HashInsert(pHash
, zName
, pTrig
);
329 }else if( pLink
->pSchema
==pLink
->pTabSchema
){
331 pTab
= sqlite3HashFind(&pLink
->pTabSchema
->tblHash
, pLink
->table
);
333 pLink
->pNext
= pTab
->pTrigger
;
334 pTab
->pTrigger
= pLink
;
338 triggerfinish_cleanup
:
339 sqlite3DeleteTrigger(db
, pTrig
);
340 assert( !pParse
->pNewTrigger
);
341 sqlite3DeleteTriggerStep(db
, pStepList
);
345 ** Duplicate a range of text from an SQL statement, then convert all
346 ** whitespace characters into ordinary space characters.
348 static char *triggerSpanDup(sqlite3
*db
, const char *zStart
, const char *zEnd
){
349 char *z
= sqlite3DbSpanDup(db
, zStart
, zEnd
);
351 if( z
) for(i
=0; z
[i
]; i
++) if( sqlite3Isspace(z
[i
]) ) z
[i
] = ' ';
356 ** Turn a SELECT statement (that the pSelect parameter points to) into
357 ** a trigger step. Return a pointer to a TriggerStep structure.
359 ** The parser calls this routine when it finds a SELECT statement in
360 ** body of a TRIGGER.
362 TriggerStep
*sqlite3TriggerSelectStep(
363 sqlite3
*db
, /* Database connection */
364 Select
*pSelect
, /* The SELECT statement */
365 const char *zStart
, /* Start of SQL text */
366 const char *zEnd
/* End of SQL text */
368 TriggerStep
*pTriggerStep
= sqlite3DbMallocZero(db
, sizeof(TriggerStep
));
369 if( pTriggerStep
==0 ) {
370 sqlite3SelectDelete(db
, pSelect
);
373 pTriggerStep
->op
= TK_SELECT
;
374 pTriggerStep
->pSelect
= pSelect
;
375 pTriggerStep
->orconf
= OE_Default
;
376 pTriggerStep
->zSpan
= triggerSpanDup(db
, zStart
, zEnd
);
381 ** Allocate space to hold a new trigger step. The allocated space
382 ** holds both the TriggerStep object and the TriggerStep.target.z string.
384 ** If an OOM error occurs, NULL is returned and db->mallocFailed is set.
386 static TriggerStep
*triggerStepAllocate(
387 sqlite3
*db
, /* Database connection */
388 u8 op
, /* Trigger opcode */
389 Token
*pName
, /* The target name */
390 const char *zStart
, /* Start of SQL text */
391 const char *zEnd
/* End of SQL text */
393 TriggerStep
*pTriggerStep
;
395 pTriggerStep
= sqlite3DbMallocZero(db
, sizeof(TriggerStep
) + pName
->n
+ 1);
397 char *z
= (char*)&pTriggerStep
[1];
398 memcpy(z
, pName
->z
, pName
->n
);
400 pTriggerStep
->zTarget
= z
;
401 pTriggerStep
->op
= op
;
402 pTriggerStep
->zSpan
= triggerSpanDup(db
, zStart
, zEnd
);
408 ** Build a trigger step out of an INSERT statement. Return a pointer
409 ** to the new trigger step.
411 ** The parser calls this routine when it sees an INSERT inside the
412 ** body of a trigger.
414 TriggerStep
*sqlite3TriggerInsertStep(
415 sqlite3
*db
, /* The database connection */
416 Token
*pTableName
, /* Name of the table into which we insert */
417 IdList
*pColumn
, /* List of columns in pTableName to insert into */
418 Select
*pSelect
, /* A SELECT statement that supplies values */
419 u8 orconf
, /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */
420 Upsert
*pUpsert
, /* ON CONFLICT clauses for upsert */
421 const char *zStart
, /* Start of SQL text */
422 const char *zEnd
/* End of SQL text */
424 TriggerStep
*pTriggerStep
;
426 assert(pSelect
!= 0 || db
->mallocFailed
);
428 pTriggerStep
= triggerStepAllocate(db
, TK_INSERT
, pTableName
, zStart
, zEnd
);
430 pTriggerStep
->pSelect
= sqlite3SelectDup(db
, pSelect
, EXPRDUP_REDUCE
);
431 pTriggerStep
->pIdList
= pColumn
;
432 pTriggerStep
->pUpsert
= pUpsert
;
433 pTriggerStep
->orconf
= orconf
;
436 sqlite3IdListDelete(db
, pColumn
);
438 sqlite3UpsertDelete(db
, pUpsert
);
440 sqlite3SelectDelete(db
, pSelect
);
446 ** Construct a trigger step that implements an UPDATE statement and return
447 ** a pointer to that trigger step. The parser calls this routine when it
448 ** sees an UPDATE statement inside the body of a CREATE TRIGGER.
450 TriggerStep
*sqlite3TriggerUpdateStep(
451 sqlite3
*db
, /* The database connection */
452 Token
*pTableName
, /* Name of the table to be updated */
453 ExprList
*pEList
, /* The SET clause: list of column and new values */
454 Expr
*pWhere
, /* The WHERE clause */
455 u8 orconf
, /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */
456 const char *zStart
, /* Start of SQL text */
457 const char *zEnd
/* End of SQL text */
459 TriggerStep
*pTriggerStep
;
461 pTriggerStep
= triggerStepAllocate(db
, TK_UPDATE
, pTableName
, zStart
, zEnd
);
463 pTriggerStep
->pExprList
= sqlite3ExprListDup(db
, pEList
, EXPRDUP_REDUCE
);
464 pTriggerStep
->pWhere
= sqlite3ExprDup(db
, pWhere
, EXPRDUP_REDUCE
);
465 pTriggerStep
->orconf
= orconf
;
467 sqlite3ExprListDelete(db
, pEList
);
468 sqlite3ExprDelete(db
, pWhere
);
473 ** Construct a trigger step that implements a DELETE statement and return
474 ** a pointer to that trigger step. The parser calls this routine when it
475 ** sees a DELETE statement inside the body of a CREATE TRIGGER.
477 TriggerStep
*sqlite3TriggerDeleteStep(
478 sqlite3
*db
, /* Database connection */
479 Token
*pTableName
, /* The table from which rows are deleted */
480 Expr
*pWhere
, /* The WHERE clause */
481 const char *zStart
, /* Start of SQL text */
482 const char *zEnd
/* End of SQL text */
484 TriggerStep
*pTriggerStep
;
486 pTriggerStep
= triggerStepAllocate(db
, TK_DELETE
, pTableName
, zStart
, zEnd
);
488 pTriggerStep
->pWhere
= sqlite3ExprDup(db
, pWhere
, EXPRDUP_REDUCE
);
489 pTriggerStep
->orconf
= OE_Default
;
491 sqlite3ExprDelete(db
, pWhere
);
496 ** Recursively delete a Trigger structure
498 void sqlite3DeleteTrigger(sqlite3
*db
, Trigger
*pTrigger
){
499 if( pTrigger
==0 ) return;
500 sqlite3DeleteTriggerStep(db
, pTrigger
->step_list
);
501 sqlite3DbFree(db
, pTrigger
->zName
);
502 sqlite3DbFree(db
, pTrigger
->table
);
503 sqlite3ExprDelete(db
, pTrigger
->pWhen
);
504 sqlite3IdListDelete(db
, pTrigger
->pColumns
);
505 sqlite3DbFree(db
, pTrigger
);
509 ** This function is called to drop a trigger from the database schema.
511 ** This may be called directly from the parser and therefore identifies
512 ** the trigger by name. The sqlite3DropTriggerPtr() routine does the
513 ** same job as this routine except it takes a pointer to the trigger
514 ** instead of the trigger name.
516 void sqlite3DropTrigger(Parse
*pParse
, SrcList
*pName
, int noErr
){
517 Trigger
*pTrigger
= 0;
521 sqlite3
*db
= pParse
->db
;
523 if( db
->mallocFailed
) goto drop_trigger_cleanup
;
524 if( SQLITE_OK
!=sqlite3ReadSchema(pParse
) ){
525 goto drop_trigger_cleanup
;
528 assert( pName
->nSrc
==1 );
529 zDb
= pName
->a
[0].zDatabase
;
530 zName
= pName
->a
[0].zName
;
531 assert( zDb
!=0 || sqlite3BtreeHoldsAllMutexes(db
) );
532 for(i
=OMIT_TEMPDB
; i
<db
->nDb
; i
++){
533 int j
= (i
<2) ? i
^1 : i
; /* Search TEMP before MAIN */
534 if( zDb
&& sqlite3StrICmp(db
->aDb
[j
].zDbSName
, zDb
) ) continue;
535 assert( sqlite3SchemaMutexHeld(db
, j
, 0) );
536 pTrigger
= sqlite3HashFind(&(db
->aDb
[j
].pSchema
->trigHash
), zName
);
537 if( pTrigger
) break;
541 sqlite3ErrorMsg(pParse
, "no such trigger: %S", pName
, 0);
543 sqlite3CodeVerifyNamedSchema(pParse
, zDb
);
545 pParse
->checkSchema
= 1;
546 goto drop_trigger_cleanup
;
548 sqlite3DropTriggerPtr(pParse
, pTrigger
);
550 drop_trigger_cleanup
:
551 sqlite3SrcListDelete(db
, pName
);
555 ** Return a pointer to the Table structure for the table that a trigger
558 static Table
*tableOfTrigger(Trigger
*pTrigger
){
559 return sqlite3HashFind(&pTrigger
->pTabSchema
->tblHash
, pTrigger
->table
);
564 ** Drop a trigger given a pointer to that trigger.
566 void sqlite3DropTriggerPtr(Parse
*pParse
, Trigger
*pTrigger
){
569 sqlite3
*db
= pParse
->db
;
572 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTrigger
->pSchema
);
573 assert( iDb
>=0 && iDb
<db
->nDb
);
574 pTable
= tableOfTrigger(pTrigger
);
576 assert( pTable
->pSchema
==pTrigger
->pSchema
|| iDb
==1 );
577 #ifndef SQLITE_OMIT_AUTHORIZATION
579 int code
= SQLITE_DROP_TRIGGER
;
580 const char *zDb
= db
->aDb
[iDb
].zDbSName
;
581 const char *zTab
= SCHEMA_TABLE(iDb
);
582 if( iDb
==1 ) code
= SQLITE_DROP_TEMP_TRIGGER
;
583 if( sqlite3AuthCheck(pParse
, code
, pTrigger
->zName
, pTable
->zName
, zDb
) ||
584 sqlite3AuthCheck(pParse
, SQLITE_DELETE
, zTab
, 0, zDb
) ){
590 /* Generate code to destroy the database record of the trigger.
593 if( (v
= sqlite3GetVdbe(pParse
))!=0 ){
594 sqlite3NestedParse(pParse
,
595 "DELETE FROM %Q.%s WHERE name=%Q AND type='trigger'",
596 db
->aDb
[iDb
].zDbSName
, MASTER_NAME
, pTrigger
->zName
598 sqlite3ChangeCookie(pParse
, iDb
);
599 sqlite3VdbeAddOp4(v
, OP_DropTrigger
, iDb
, 0, 0, pTrigger
->zName
, 0);
604 ** Remove a trigger from the hash tables of the sqlite* pointer.
606 void sqlite3UnlinkAndDeleteTrigger(sqlite3
*db
, int iDb
, const char *zName
){
610 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
611 pHash
= &(db
->aDb
[iDb
].pSchema
->trigHash
);
612 pTrigger
= sqlite3HashInsert(pHash
, zName
, 0);
613 if( ALWAYS(pTrigger
) ){
614 if( pTrigger
->pSchema
==pTrigger
->pTabSchema
){
615 Table
*pTab
= tableOfTrigger(pTrigger
);
617 for(pp
=&pTab
->pTrigger
; *pp
!=pTrigger
; pp
=&((*pp
)->pNext
));
620 sqlite3DeleteTrigger(db
, pTrigger
);
621 db
->mDbFlags
|= DBFLAG_SchemaChange
;
626 ** pEList is the SET clause of an UPDATE statement. Each entry
627 ** in pEList is of the format <id>=<expr>. If any of the entries
628 ** in pEList have an <id> which matches an identifier in pIdList,
629 ** then return TRUE. If pIdList==NULL, then it is considered a
630 ** wildcard that matches anything. Likewise if pEList==NULL then
631 ** it matches anything so always return true. Return false only
632 ** if there is no match.
634 static int checkColumnOverlap(IdList
*pIdList
, ExprList
*pEList
){
636 if( pIdList
==0 || NEVER(pEList
==0) ) return 1;
637 for(e
=0; e
<pEList
->nExpr
; e
++){
638 if( sqlite3IdListIndex(pIdList
, pEList
->a
[e
].zName
)>=0 ) return 1;
644 ** Return a list of all triggers on table pTab if there exists at least
645 ** one trigger that must be fired when an operation of type 'op' is
646 ** performed on the table, and, if that operation is an UPDATE, if at
647 ** least one of the columns in pChanges is being modified.
649 Trigger
*sqlite3TriggersExist(
650 Parse
*pParse
, /* Parse context */
651 Table
*pTab
, /* The table the contains the triggers */
652 int op
, /* one of TK_DELETE, TK_INSERT, TK_UPDATE */
653 ExprList
*pChanges
, /* Columns that change in an UPDATE statement */
654 int *pMask
/* OUT: Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
660 if( (pParse
->db
->flags
& SQLITE_EnableTrigger
)!=0 ){
661 pList
= sqlite3TriggerList(pParse
, pTab
);
663 assert( pList
==0 || IsVirtual(pTab
)==0 );
664 for(p
=pList
; p
; p
=p
->pNext
){
665 if( p
->op
==op
&& checkColumnOverlap(p
->pColumns
, pChanges
) ){
672 return (mask
? pList
: 0);
676 ** Convert the pStep->zTarget string into a SrcList and return a pointer
679 ** This routine adds a specific database name, if needed, to the target when
680 ** forming the SrcList. This prevents a trigger in one database from
681 ** referring to a target in another database. An exception is when the
682 ** trigger is in TEMP in which case it can refer to any other database it
685 static SrcList
*targetSrcList(
686 Parse
*pParse
, /* The parsing context */
687 TriggerStep
*pStep
/* The trigger containing the target token */
689 sqlite3
*db
= pParse
->db
;
690 int iDb
; /* Index of the database to use */
691 SrcList
*pSrc
; /* SrcList to be returned */
693 pSrc
= sqlite3SrcListAppend(db
, 0, 0, 0);
695 assert( pSrc
->nSrc
>0 );
696 pSrc
->a
[pSrc
->nSrc
-1].zName
= sqlite3DbStrDup(db
, pStep
->zTarget
);
697 iDb
= sqlite3SchemaToIndex(db
, pStep
->pTrig
->pSchema
);
698 if( iDb
==0 || iDb
>=2 ){
700 assert( iDb
<db
->nDb
);
701 zDb
= db
->aDb
[iDb
].zDbSName
;
702 pSrc
->a
[pSrc
->nSrc
-1].zDatabase
= sqlite3DbStrDup(db
, zDb
);
709 ** Generate VDBE code for the statements inside the body of a single
712 static int codeTriggerProgram(
713 Parse
*pParse
, /* The parser context */
714 TriggerStep
*pStepList
, /* List of statements inside the trigger body */
715 int orconf
/* Conflict algorithm. (OE_Abort, etc) */
718 Vdbe
*v
= pParse
->pVdbe
;
719 sqlite3
*db
= pParse
->db
;
721 assert( pParse
->pTriggerTab
&& pParse
->pToplevel
);
724 for(pStep
=pStepList
; pStep
; pStep
=pStep
->pNext
){
725 /* Figure out the ON CONFLICT policy that will be used for this step
726 ** of the trigger program. If the statement that caused this trigger
727 ** to fire had an explicit ON CONFLICT, then use it. Otherwise, use
728 ** the ON CONFLICT policy that was specified as part of the trigger
729 ** step statement. Example:
731 ** CREATE TRIGGER AFTER INSERT ON t1 BEGIN;
732 ** INSERT OR REPLACE INTO t2 VALUES(new.a, new.b);
735 ** INSERT INTO t1 ... ; -- insert into t2 uses REPLACE policy
736 ** INSERT OR IGNORE INTO t1 ... ; -- insert into t2 uses IGNORE policy
738 pParse
->eOrconf
= (orconf
==OE_Default
)?pStep
->orconf
:(u8
)orconf
;
739 assert( pParse
->okConstFactor
==0 );
741 #ifndef SQLITE_OMIT_TRACE
743 sqlite3VdbeAddOp4(v
, OP_Trace
, 0x7fffffff, 1, 0,
744 sqlite3MPrintf(db
, "-- %s", pStep
->zSpan
),
751 sqlite3Update(pParse
,
752 targetSrcList(pParse
, pStep
),
753 sqlite3ExprListDup(db
, pStep
->pExprList
, 0),
754 sqlite3ExprDup(db
, pStep
->pWhere
, 0),
755 pParse
->eOrconf
, 0, 0, 0
760 sqlite3Insert(pParse
,
761 targetSrcList(pParse
, pStep
),
762 sqlite3SelectDup(db
, pStep
->pSelect
, 0),
763 sqlite3IdListDup(db
, pStep
->pIdList
),
765 sqlite3UpsertDup(db
, pStep
->pUpsert
)
770 sqlite3DeleteFrom(pParse
,
771 targetSrcList(pParse
, pStep
),
772 sqlite3ExprDup(db
, pStep
->pWhere
, 0), 0, 0
776 default: assert( pStep
->op
==TK_SELECT
); {
778 Select
*pSelect
= sqlite3SelectDup(db
, pStep
->pSelect
, 0);
779 sqlite3SelectDestInit(&sDest
, SRT_Discard
, 0);
780 sqlite3Select(pParse
, pSelect
, &sDest
);
781 sqlite3SelectDelete(db
, pSelect
);
785 if( pStep
->op
!=TK_SELECT
){
786 sqlite3VdbeAddOp0(v
, OP_ResetCount
);
793 #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
795 ** This function is used to add VdbeComment() annotations to a VDBE
796 ** program. It is not used in production code, only for debugging.
798 static const char *onErrorText(int onError
){
800 case OE_Abort
: return "abort";
801 case OE_Rollback
: return "rollback";
802 case OE_Fail
: return "fail";
803 case OE_Replace
: return "replace";
804 case OE_Ignore
: return "ignore";
805 case OE_Default
: return "default";
812 ** Parse context structure pFrom has just been used to create a sub-vdbe
813 ** (trigger program). If an error has occurred, transfer error information
814 ** from pFrom to pTo.
816 static void transferParseError(Parse
*pTo
, Parse
*pFrom
){
817 assert( pFrom
->zErrMsg
==0 || pFrom
->nErr
);
818 assert( pTo
->zErrMsg
==0 || pTo
->nErr
);
820 pTo
->zErrMsg
= pFrom
->zErrMsg
;
821 pTo
->nErr
= pFrom
->nErr
;
824 sqlite3DbFree(pFrom
->db
, pFrom
->zErrMsg
);
829 ** Create and populate a new TriggerPrg object with a sub-program
830 ** implementing trigger pTrigger with ON CONFLICT policy orconf.
832 static TriggerPrg
*codeRowTrigger(
833 Parse
*pParse
, /* Current parse context */
834 Trigger
*pTrigger
, /* Trigger to code */
835 Table
*pTab
, /* The table pTrigger is attached to */
836 int orconf
/* ON CONFLICT policy to code trigger program with */
838 Parse
*pTop
= sqlite3ParseToplevel(pParse
);
839 sqlite3
*db
= pParse
->db
; /* Database handle */
840 TriggerPrg
*pPrg
; /* Value to return */
841 Expr
*pWhen
= 0; /* Duplicate of trigger WHEN expression */
842 Vdbe
*v
; /* Temporary VM */
843 NameContext sNC
; /* Name context for sub-vdbe */
844 SubProgram
*pProgram
= 0; /* Sub-vdbe for trigger program */
845 Parse
*pSubParse
; /* Parse context for sub-vdbe */
846 int iEndTrigger
= 0; /* Label to jump to if WHEN is false */
848 assert( pTrigger
->zName
==0 || pTab
==tableOfTrigger(pTrigger
) );
849 assert( pTop
->pVdbe
);
851 /* Allocate the TriggerPrg and SubProgram objects. To ensure that they
852 ** are freed if an error occurs, link them into the Parse.pTriggerPrg
853 ** list of the top-level Parse object sooner rather than later. */
854 pPrg
= sqlite3DbMallocZero(db
, sizeof(TriggerPrg
));
855 if( !pPrg
) return 0;
856 pPrg
->pNext
= pTop
->pTriggerPrg
;
857 pTop
->pTriggerPrg
= pPrg
;
858 pPrg
->pProgram
= pProgram
= sqlite3DbMallocZero(db
, sizeof(SubProgram
));
859 if( !pProgram
) return 0;
860 sqlite3VdbeLinkSubProgram(pTop
->pVdbe
, pProgram
);
861 pPrg
->pTrigger
= pTrigger
;
862 pPrg
->orconf
= orconf
;
863 pPrg
->aColmask
[0] = 0xffffffff;
864 pPrg
->aColmask
[1] = 0xffffffff;
866 /* Allocate and populate a new Parse context to use for coding the
867 ** trigger sub-program. */
868 pSubParse
= sqlite3StackAllocZero(db
, sizeof(Parse
));
869 if( !pSubParse
) return 0;
870 memset(&sNC
, 0, sizeof(sNC
));
871 sNC
.pParse
= pSubParse
;
873 pSubParse
->pTriggerTab
= pTab
;
874 pSubParse
->pToplevel
= pTop
;
875 pSubParse
->zAuthContext
= pTrigger
->zName
;
876 pSubParse
->eTriggerOp
= pTrigger
->op
;
877 pSubParse
->nQueryLoop
= pParse
->nQueryLoop
;
879 v
= sqlite3GetVdbe(pSubParse
);
881 VdbeComment((v
, "Start: %s.%s (%s %s%s%s ON %s)",
882 pTrigger
->zName
, onErrorText(orconf
),
883 (pTrigger
->tr_tm
==TRIGGER_BEFORE
? "BEFORE" : "AFTER"),
884 (pTrigger
->op
==TK_UPDATE
? "UPDATE" : ""),
885 (pTrigger
->op
==TK_INSERT
? "INSERT" : ""),
886 (pTrigger
->op
==TK_DELETE
? "DELETE" : ""),
889 #ifndef SQLITE_OMIT_TRACE
890 if( pTrigger
->zName
){
891 sqlite3VdbeChangeP4(v
, -1,
892 sqlite3MPrintf(db
, "-- TRIGGER %s", pTrigger
->zName
), P4_DYNAMIC
897 /* If one was specified, code the WHEN clause. If it evaluates to false
898 ** (or NULL) the sub-vdbe is immediately halted by jumping to the
899 ** OP_Halt inserted at the end of the program. */
900 if( pTrigger
->pWhen
){
901 pWhen
= sqlite3ExprDup(db
, pTrigger
->pWhen
, 0);
902 if( SQLITE_OK
==sqlite3ResolveExprNames(&sNC
, pWhen
)
903 && db
->mallocFailed
==0
905 iEndTrigger
= sqlite3VdbeMakeLabel(v
);
906 sqlite3ExprIfFalse(pSubParse
, pWhen
, iEndTrigger
, SQLITE_JUMPIFNULL
);
908 sqlite3ExprDelete(db
, pWhen
);
911 /* Code the trigger program into the sub-vdbe. */
912 codeTriggerProgram(pSubParse
, pTrigger
->step_list
, orconf
);
914 /* Insert an OP_Halt at the end of the sub-program. */
916 sqlite3VdbeResolveLabel(v
, iEndTrigger
);
918 sqlite3VdbeAddOp0(v
, OP_Halt
);
919 VdbeComment((v
, "End: %s.%s", pTrigger
->zName
, onErrorText(orconf
)));
921 transferParseError(pParse
, pSubParse
);
922 if( db
->mallocFailed
==0 && pParse
->nErr
==0 ){
923 pProgram
->aOp
= sqlite3VdbeTakeOpArray(v
, &pProgram
->nOp
, &pTop
->nMaxArg
);
925 pProgram
->nMem
= pSubParse
->nMem
;
926 pProgram
->nCsr
= pSubParse
->nTab
;
927 pProgram
->token
= (void *)pTrigger
;
928 pPrg
->aColmask
[0] = pSubParse
->oldmask
;
929 pPrg
->aColmask
[1] = pSubParse
->newmask
;
930 sqlite3VdbeDelete(v
);
933 assert( !pSubParse
->pAinc
&& !pSubParse
->pZombieTab
);
934 assert( !pSubParse
->pTriggerPrg
&& !pSubParse
->nMaxArg
);
935 sqlite3ParserReset(pSubParse
);
936 sqlite3StackFree(db
, pSubParse
);
942 ** Return a pointer to a TriggerPrg object containing the sub-program for
943 ** trigger pTrigger with default ON CONFLICT algorithm orconf. If no such
944 ** TriggerPrg object exists, a new object is allocated and populated before
947 static TriggerPrg
*getRowTrigger(
948 Parse
*pParse
, /* Current parse context */
949 Trigger
*pTrigger
, /* Trigger to code */
950 Table
*pTab
, /* The table trigger pTrigger is attached to */
951 int orconf
/* ON CONFLICT algorithm. */
953 Parse
*pRoot
= sqlite3ParseToplevel(pParse
);
956 assert( pTrigger
->zName
==0 || pTab
==tableOfTrigger(pTrigger
) );
958 /* It may be that this trigger has already been coded (or is in the
959 ** process of being coded). If this is the case, then an entry with
960 ** a matching TriggerPrg.pTrigger field will be present somewhere
961 ** in the Parse.pTriggerPrg list. Search for such an entry. */
962 for(pPrg
=pRoot
->pTriggerPrg
;
963 pPrg
&& (pPrg
->pTrigger
!=pTrigger
|| pPrg
->orconf
!=orconf
);
967 /* If an existing TriggerPrg could not be located, create a new one. */
969 pPrg
= codeRowTrigger(pParse
, pTrigger
, pTab
, orconf
);
976 ** Generate code for the trigger program associated with trigger p on
977 ** table pTab. The reg, orconf and ignoreJump parameters passed to this
978 ** function are the same as those described in the header function for
979 ** sqlite3CodeRowTrigger()
981 void sqlite3CodeRowTriggerDirect(
982 Parse
*pParse
, /* Parse context */
983 Trigger
*p
, /* Trigger to code */
984 Table
*pTab
, /* The table to code triggers from */
985 int reg
, /* Reg array containing OLD.* and NEW.* values */
986 int orconf
, /* ON CONFLICT policy */
987 int ignoreJump
/* Instruction to jump to for RAISE(IGNORE) */
989 Vdbe
*v
= sqlite3GetVdbe(pParse
); /* Main VM */
991 pPrg
= getRowTrigger(pParse
, p
, pTab
, orconf
);
992 assert( pPrg
|| pParse
->nErr
|| pParse
->db
->mallocFailed
);
994 /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program
995 ** is a pointer to the sub-vdbe containing the trigger program. */
997 int bRecursive
= (p
->zName
&& 0==(pParse
->db
->flags
&SQLITE_RecTriggers
));
999 sqlite3VdbeAddOp4(v
, OP_Program
, reg
, ignoreJump
, ++pParse
->nMem
,
1000 (const char *)pPrg
->pProgram
, P4_SUBPROGRAM
);
1002 (v
, "Call: %s.%s", (p
->zName
?p
->zName
:"fkey"), onErrorText(orconf
)));
1004 /* Set the P5 operand of the OP_Program instruction to non-zero if
1005 ** recursive invocation of this trigger program is disallowed. Recursive
1006 ** invocation is disallowed if (a) the sub-program is really a trigger,
1007 ** not a foreign key action, and (b) the flag to enable recursive triggers
1009 sqlite3VdbeChangeP5(v
, (u8
)bRecursive
);
1014 ** This is called to code the required FOR EACH ROW triggers for an operation
1015 ** on table pTab. The operation to code triggers for (INSERT, UPDATE or DELETE)
1016 ** is given by the op parameter. The tr_tm parameter determines whether the
1017 ** BEFORE or AFTER triggers are coded. If the operation is an UPDATE, then
1018 ** parameter pChanges is passed the list of columns being modified.
1020 ** If there are no triggers that fire at the specified time for the specified
1021 ** operation on pTab, this function is a no-op.
1023 ** The reg argument is the address of the first in an array of registers
1024 ** that contain the values substituted for the new.* and old.* references
1025 ** in the trigger program. If N is the number of columns in table pTab
1026 ** (a copy of pTab->nCol), then registers are populated as follows:
1028 ** Register Contains
1029 ** ------------------------------------------------------
1031 ** reg+1 OLD.* value of left-most column of pTab
1033 ** reg+N OLD.* value of right-most column of pTab
1034 ** reg+N+1 NEW.rowid
1035 ** reg+N+2 OLD.* value of left-most column of pTab
1037 ** reg+N+N+1 NEW.* value of right-most column of pTab
1039 ** For ON DELETE triggers, the registers containing the NEW.* values will
1040 ** never be accessed by the trigger program, so they are not allocated or
1041 ** populated by the caller (there is no data to populate them with anyway).
1042 ** Similarly, for ON INSERT triggers the values stored in the OLD.* registers
1043 ** are never accessed, and so are not allocated by the caller. So, for an
1044 ** ON INSERT trigger, the value passed to this function as parameter reg
1045 ** is not a readable register, although registers (reg+N) through
1048 ** Parameter orconf is the default conflict resolution algorithm for the
1049 ** trigger program to use (REPLACE, IGNORE etc.). Parameter ignoreJump
1050 ** is the instruction that control should jump to if a trigger program
1051 ** raises an IGNORE exception.
1053 void sqlite3CodeRowTrigger(
1054 Parse
*pParse
, /* Parse context */
1055 Trigger
*pTrigger
, /* List of triggers on table pTab */
1056 int op
, /* One of TK_UPDATE, TK_INSERT, TK_DELETE */
1057 ExprList
*pChanges
, /* Changes list for any UPDATE OF triggers */
1058 int tr_tm
, /* One of TRIGGER_BEFORE, TRIGGER_AFTER */
1059 Table
*pTab
, /* The table to code triggers from */
1060 int reg
, /* The first in an array of registers (see above) */
1061 int orconf
, /* ON CONFLICT policy */
1062 int ignoreJump
/* Instruction to jump to for RAISE(IGNORE) */
1064 Trigger
*p
; /* Used to iterate through pTrigger list */
1066 assert( op
==TK_UPDATE
|| op
==TK_INSERT
|| op
==TK_DELETE
);
1067 assert( tr_tm
==TRIGGER_BEFORE
|| tr_tm
==TRIGGER_AFTER
);
1068 assert( (op
==TK_UPDATE
)==(pChanges
!=0) );
1070 for(p
=pTrigger
; p
; p
=p
->pNext
){
1072 /* Sanity checking: The schema for the trigger and for the table are
1073 ** always defined. The trigger must be in the same schema as the table
1074 ** or else it must be a TEMP trigger. */
1075 assert( p
->pSchema
!=0 );
1076 assert( p
->pTabSchema
!=0 );
1077 assert( p
->pSchema
==p
->pTabSchema
1078 || p
->pSchema
==pParse
->db
->aDb
[1].pSchema
);
1080 /* Determine whether we should code this trigger */
1083 && checkColumnOverlap(p
->pColumns
, pChanges
)
1085 sqlite3CodeRowTriggerDirect(pParse
, p
, pTab
, reg
, orconf
, ignoreJump
);
1091 ** Triggers may access values stored in the old.* or new.* pseudo-table.
1092 ** This function returns a 32-bit bitmask indicating which columns of the
1093 ** old.* or new.* tables actually are used by triggers. This information
1094 ** may be used by the caller, for example, to avoid having to load the entire
1095 ** old.* record into memory when executing an UPDATE or DELETE command.
1097 ** Bit 0 of the returned mask is set if the left-most column of the
1098 ** table may be accessed using an [old|new].<col> reference. Bit 1 is set if
1099 ** the second leftmost column value is required, and so on. If there
1100 ** are more than 32 columns in the table, and at least one of the columns
1101 ** with an index greater than 32 may be accessed, 0xffffffff is returned.
1103 ** It is not possible to determine if the old.rowid or new.rowid column is
1104 ** accessed by triggers. The caller must always assume that it is.
1106 ** Parameter isNew must be either 1 or 0. If it is 0, then the mask returned
1107 ** applies to the old.* table. If 1, the new.* table.
1109 ** Parameter tr_tm must be a mask with one or both of the TRIGGER_BEFORE
1110 ** and TRIGGER_AFTER bits set. Values accessed by BEFORE triggers are only
1111 ** included in the returned mask if the TRIGGER_BEFORE bit is set in the
1112 ** tr_tm parameter. Similarly, values accessed by AFTER triggers are only
1113 ** included in the returned mask if the TRIGGER_AFTER bit is set in tr_tm.
1115 u32
sqlite3TriggerColmask(
1116 Parse
*pParse
, /* Parse context */
1117 Trigger
*pTrigger
, /* List of triggers on table pTab */
1118 ExprList
*pChanges
, /* Changes list for any UPDATE OF triggers */
1119 int isNew
, /* 1 for new.* ref mask, 0 for old.* ref mask */
1120 int tr_tm
, /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
1121 Table
*pTab
, /* The table to code triggers from */
1122 int orconf
/* Default ON CONFLICT policy for trigger steps */
1124 const int op
= pChanges
? TK_UPDATE
: TK_DELETE
;
1128 assert( isNew
==1 || isNew
==0 );
1129 for(p
=pTrigger
; p
; p
=p
->pNext
){
1130 if( p
->op
==op
&& (tr_tm
&p
->tr_tm
)
1131 && checkColumnOverlap(p
->pColumns
,pChanges
)
1134 pPrg
= getRowTrigger(pParse
, p
, pTab
, orconf
);
1136 mask
|= pPrg
->aColmask
[isNew
];
1144 #endif /* !defined(SQLITE_OMIT_TRIGGER) */