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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
