SQLite

Check-in [991ca9b2]
Login

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

Overview
Comment:Suppress errors associated with TEMP triggers that reference objects in non-TEMP databases. This is a continuation of the fix for ticket #3810 shown in check-in [ba1afc040171810d] from 2009-08-06, based on a bug report in forum post 157dc791df
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 991ca9b26bacd8f6b64498057fe28f2068466a220f372fd365b6685f583f0e92
User & Date: drh 2020-11-05 19:13:44
Context
2020-11-06
16:48
Add ALWAYS() to conditionals associated with SHM locking that are always true. (check-in: b599e890 user: drh tags: trunk)
2020-11-05
19:13
Suppress errors associated with TEMP triggers that reference objects in non-TEMP databases. This is a continuation of the fix for ticket #3810 shown in check-in [ba1afc040171810d] from 2009-08-06, based on a bug report in forum post 157dc791df (check-in: 991ca9b2 user: drh tags: trunk)
14:50
Remove unused variable from speedtest1.c (check-in: c0a18565 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/trigger.c.

︙︙
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182

  sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName);
  if( sqlite3FixSrcList(&sFix, pTableName) ){
    goto trigger_cleanup;
  }
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( !pTab ){
    /* The table does not exist. */
    if( db->init.iDb==1 ){
      /* Ticket #3810.
      ** Normally, whenever a table is dropped, all associated triggers are
      ** dropped too.  But if a TEMP trigger is created on a non-TEMP table
      ** and the table is dropped by a different database connection, the
      ** trigger is not visible to the database connection that does the
      ** drop so the trigger cannot be dropped.  This results in an
      ** "orphaned trigger" - a trigger whose associated table is missing.
      */
      db->init.orphanTrigger = 1;
    }
    goto trigger_cleanup;
  }
  if( IsVirtual(pTab) ){
    sqlite3ErrorMsg(pParse, "cannot create triggers on virtual tables");
    goto trigger_cleanup;
  }

  /* Check that the trigger name is not reserved and that no trigger of the
  ** specified name exists */
  zName = sqlite3NameFromToken(db, pName);
  if( zName==0 ){
    assert( db->mallocFailed );








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



|








153
154
155
156
157
158
159











160
161
162
163
164
165
166
167
168
169
170
171

  sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName);
  if( sqlite3FixSrcList(&sFix, pTableName) ){
    goto trigger_cleanup;
  }
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( !pTab ){
    /* The table does not exist. */











    goto trigger_orphan_error;
  }
  if( IsVirtual(pTab) ){
    sqlite3ErrorMsg(pParse, "cannot create triggers on virtual tables");
    goto trigger_orphan_error;
  }

  /* Check that the trigger name is not reserved and that no trigger of the
  ** specified name exists */
  zName = sqlite3NameFromToken(db, pName);
  if( zName==0 ){
    assert( db->mallocFailed );
︙︙
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225


  /* INSTEAD of triggers are only for views and views only support INSTEAD
  ** of triggers.
  */
  if( pTab->pSelect && tr_tm!=TK_INSTEAD ){
    sqlite3ErrorMsg(pParse, "cannot create %s trigger on view: %S", 
        (tr_tm == TK_BEFORE)?"BEFORE":"AFTER", pTableName, 0);
    goto trigger_cleanup;
  }
  if( !pTab->pSelect && tr_tm==TK_INSTEAD ){
    sqlite3ErrorMsg(pParse, "cannot create INSTEAD OF"
        " trigger on table: %S", pTableName, 0);
    goto trigger_cleanup;
  }

#ifndef SQLITE_OMIT_AUTHORIZATION
  if( !IN_RENAME_OBJECT ){
    int iTabDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    int code = SQLITE_CREATE_TRIGGER;
    const char *zDb = db->aDb[iTabDb].zDbSName;








|




|








195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214


  /* INSTEAD of triggers are only for views and views only support INSTEAD
  ** of triggers.
  */
  if( pTab->pSelect && tr_tm!=TK_INSTEAD ){
    sqlite3ErrorMsg(pParse, "cannot create %s trigger on view: %S", 
        (tr_tm == TK_BEFORE)?"BEFORE":"AFTER", pTableName, 0);
    goto trigger_orphan_error;
  }
  if( !pTab->pSelect && tr_tm==TK_INSTEAD ){
    sqlite3ErrorMsg(pParse, "cannot create INSTEAD OF"
        " trigger on table: %S", pTableName, 0);
    goto trigger_orphan_error;
  }

#ifndef SQLITE_OMIT_AUTHORIZATION
  if( !IN_RENAME_OBJECT ){
    int iTabDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    int code = SQLITE_CREATE_TRIGGER;
    const char *zDb = db->aDb[iTabDb].zDbSName;
︙︙
271
272
273
274
275
276
277

















278
279
280
281
282
283
284

  sqlite3IdListDelete(db, pColumns);
  sqlite3ExprDelete(db, pWhen);
  if( !pParse->pNewTrigger ){
    sqlite3DeleteTrigger(db, pTrigger);
  }else{
    assert( pParse->pNewTrigger==pTrigger );
  }

















}

/*
** This routine is called after all of the trigger actions have been parsed
** in order to complete the process of building the trigger.
*/
void sqlite3FinishTrigger(








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








260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

  sqlite3IdListDelete(db, pColumns);
  sqlite3ExprDelete(db, pWhen);
  if( !pParse->pNewTrigger ){
    sqlite3DeleteTrigger(db, pTrigger);
  }else{
    assert( pParse->pNewTrigger==pTrigger );
  }
  return;

trigger_orphan_error:
  if( db->init.iDb==1 ){
    /* Ticket #3810.
    ** Normally, whenever a table is dropped, all associated triggers are
    ** dropped too.  But if a TEMP trigger is created on a non-TEMP table
    ** and the table is dropped by a different database connection, the
    ** trigger is not visible to the database connection that does the
    ** drop so the trigger cannot be dropped.  This results in an
    ** "orphaned trigger" - a trigger whose associated table is missing.
    **
    ** 2020-11-05 see also https://sqlite.org/forum/forumpost/157dc791df
    */
    db->init.orphanTrigger = 1;
  }
  goto trigger_cleanup;
}

/*
** This routine is called after all of the trigger actions have been parsed
** in order to complete the process of building the trigger.
*/
void sqlite3FinishTrigger(
︙︙

Changes to test/tkt3810.test.

︙︙
79
80
81
82
83
84
85
















86
87

  execsql {DROP TABLE t1}
  execsql {
    SELECT name FROM sqlite_temp_master;
  }
} {}

db2 close

















finish_test








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



79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

  execsql {DROP TABLE t1}
  execsql {
    SELECT name FROM sqlite_temp_master;
  }
} {}

db2 close

# 2020-11-06 forum post https://sqlite.org/forum/forumpost/157dc791df
#
reset_db
do_test tkt3810-100 {
  db eval {
    ATTACH ':memory:' AS aux1;
    CREATE TABLE aux1.t1(x);
    CREATE TEMP TRIGGER r1 DELETE ON t1 BEGIN SELECT *; END;
    CREATE VIEW t1 AS SELECT *;
  }
  catch {db eval {
    CREATE VIRTUAL TABLE t2 USING nosuchmodule;
  }}
  db eval {CREATE TABLE t3(z);}
} {}

finish_test