SQLite

Check-in [ec914af326]
Login

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

Overview
Comment:When creating a trigger on a main database table when there is a TEMP table with the same name, make sure the trigger is bound to the main table. Ticket [985771e11612].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ec914af32675e472694270d46f3ba2214eb2fe90
User & Date: drh 2010-02-15 16:54:55.000
Context
2010-02-15
18:03
Fix the ALTER TABLE RENAME command so that it converts FOREIGN KEY constraints in ATTACH-ed and in TEMP tables as well as in the main database. Ticket [13336e9c3c8c3f]. (check-in: ab197d0aaf user: drh tags: trunk)
16:54
When creating a trigger on a main database table when there is a TEMP table with the same name, make sure the trigger is bound to the main table. Ticket [985771e11612]. (check-in: ec914af326 user: drh tags: trunk)
15:47
Fix a compiler warning in shell.c. Updates to comments in trigger.c. (check-in: c727601eec user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/trigger.c.
︙︙
122
123
124
125
126
127
128

129
130
131
132
133
134
135
136

  ** If sqlite3SrcListLookup() returns 0, indicating the table does not
  ** exist, the error is caught by the block below.
  */
  if( !pTableName || db->mallocFailed ){
    goto trigger_cleanup;
  }
  pTab = sqlite3SrcListLookup(pParse, pTableName);

  if( pName2->n==0 && pTab && pTab->pSchema==db->aDb[1].pSchema ){
    iDb = 1;
  }

  /* Ensure the table name matches database name and that the table exists */
  if( db->mallocFailed ) goto trigger_cleanup;
  assert( pTableName->nSrc==1 );
  if( sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName) && 








>
|








122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

  ** If sqlite3SrcListLookup() returns 0, indicating the table does not
  ** exist, the error is caught by the block below.
  */
  if( !pTableName || db->mallocFailed ){
    goto trigger_cleanup;
  }
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( db->init.busy==0 && pName2->n==0 && pTab
        && pTab->pSchema==db->aDb[1].pSchema ){
    iDb = 1;
  }

  /* Ensure the table name matches database name and that the table exists */
  if( db->mallocFailed ) goto trigger_cleanup;
  assert( pTableName->nSrc==1 );
  if( sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName) &&
︙︙
Changes to test/triggerD.test.
︙︙
118
119
120
121
122
123
124
















































125
126

do_test triggerD-2.4 {
  db eval {
    DELETE FROM log;
    DELETE FROM t1;
    SELECT * FROM log
  }
} {r5 1 1 1 201 r6 1 1 1 201}

















































finish_test








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



118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

do_test triggerD-2.4 {
  db eval {
    DELETE FROM log;
    DELETE FROM t1;
    SELECT * FROM log
  }
} {r5 1 1 1 201 r6 1 1 1 201}


###########################################################################
#
# Ticket [985771e1161200ae5eac3162686ea6711c035d08]:
#
# When both a main database table and a TEMP table have the same name,
# and a main database trigge is created on the main table, the trigger
# is incorrectly bound to the TEMP table. For example:
#
#   CREATE TABLE t1(x);
#   CREATE TEMP TABLE t1(x);
#   CREATE TABLE t2(z);
#   CREATE TRIGGER main.r1 AFTER INSERT ON t1 BEGIN
#     INSERT INTO t2 VALUES(10000 + new.x);
#   END;
#   INSERT INTO main.t1 VALUES(3);
#   INSERT INTO temp.t1 VALUES(4);
#   SELECT * FROM t2;
# 
# The r1 trigger fires when the value 4 is inserted into the temp.t1
# table, rather than when value 3 is inserted into main.t1.
#
do_test triggerD-3.1 {
  db eval {
    CREATE TABLE t300(x);
    CREATE TEMP TABLE t300(x);
    CREATE TABLE t301(y);
    CREATE TRIGGER main.r300 AFTER INSERT ON t300 BEGIN
      INSERT INTO t301 VALUES(10000 + new.x);
    END;
    INSERT INTO main.t300 VALUES(3);
    INSERT INTO temp.t300 VALUES(4);
    SELECT * FROM t301;
  }
} {10003}
do_test triggerD-3.2 {
  db eval {
    DELETE FROM t301;
    CREATE TRIGGER temp.r301 AFTER INSERT ON t300 BEGIN
      INSERT INTO t301 VALUES(20000 + new.x);
    END;
    INSERT INTO main.t300 VALUES(3);
    INSERT INTO temp.t300 VALUES(4);
    SELECT * FROM t301;
  }
} {10003 20004}


finish_test