The documentation has been updated to clarify the ambiguity and to hopefully make it easier to understand.
On 6/9/17, Mark Brand <[email protected]> wrote: > According to the documentation: > > The name of the table to be modified in an UPDATE, DELETE, or INSERT > statement must be an unqualified table name. In other words, one > must use just "tablename" not "database.tablename" when specifying > the table. *The table to be modified must exist in the same database > as the table or view to which the trigger is attached.* > > http://sqlite.org/lang_createtrigger.html > > I'm struggling to understand the rationale for the first sentence, but > before getting very far I stumble on the second sentence to which the > following appears to be a counterexample. > > create table t (x); > insert into t values (0); > create temp view v as select * from t; > > /* > Trigger will be attached to view v in database temp. > But the update modifies table t exists only in main, not temp. > */ > create temp trigger trg1 instead of update on temp.v > begin > update t set x = new.x; > end; > > update v set x = 1; > select 'main', * from main.t; > > /* > main|1 > */ > > select 'main' db, tbl_name, type, name from main.sqlite_master > union select 'temp', tbl_name, type, name from temp.sqlite_master; > > /* > main|t|table|t > temp|v|trigger|trg1 > temp|v|view|v > */ > > /* > The table affected by the update in the trigger follows the > ordinary rule for resolving > unqualified table names, i.e., first temp database, then least > recently attached. > */ > > create temp table t (x); > insert into temp.t values (0); > > update main.t set x = 0; > > update v set x = 1; > > select 'main', * from main.t > union select 'temp', * from temp.t; > > /* > main|0 > temp|1 > */ > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
