The way I read the responses is that you can't specify this. You might be able to fudge things using the naming conventions where an unqualified name resolves in attached database order. But that only works if there's no table of that name in the main database as well. And wouldn't work if you want to go in the other direction either.
SQLite version 3.20.0 2017-08-01 13:24:15 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> attach database 'deleteMe1.sqlite' as db1; sqlite> create table db1.t (a, b, c); sqlite> attach database 'deleteMe2.sqlite' as db2; sqlite> create table db2.t (a, b, c); sqlite> create temp trigger trg after insert on db2.t begin insert into db1.t values (new.a, new.b, new.c); end; Error: qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers sqlite> create temp trigger trg after insert on db2.t /*attached second*/ begin insert into t /*db1, attached first*/ values (new.a, new.b, new.c); end; sqlite> insert into db2.t values (1, 2, 3); sqlite> select * from db1.t; a|b|c 1|2|3 -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Roman Fleysher Sent: Friday, August 11, 2017 3:46 PM To: SQLite mailing list Subject: Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers Dear Richard, Dear SQLiters, This is not clear to me as well. If I have two databases, db1 and db2. Both have table t. db1.t and bd2.t. I want to create a TEMPORARY trigger that upon insert in db1.t does something with db2.t. Because: TEMP triggers are not subject to the same-database rule. A TEMP trigger is allowed to query or modify any table in any ATTACH-ed database. I need to be able to specify db2.t in the body of the trigger. But this is not allowed, right? Then how could it work? I mean it does not. Should it work? Roman ________________________________________ From: sqlite-users [[email protected]] on behalf of Richard Hipp [[email protected]] Sent: Friday, June 09, 2017 9:23 AM To: SQLite mailing list Subject: Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers On 6/9/17, Mark Brand <[email protected]> wrote: > > > On 09/06/17 14:47, Richard Hipp wrote: >> The documentation has been updated to clarify the ambiguity and to >> hopefully make it easier to understand. > > Thanks. The exception for non-TEMP triggers is something I was hoping > for too: > >> For non-TEMP triggers, the table to be modified or queried must exist >> in the same database as the table or view to which the trigger is >> attached. TEMP triggers are not subject to the same-database rule. A >> TEMP trigger is allowed to query or modify any table in any ATTACH >> <https://sqlite.org/lang_attach.html>-ed database. > > But it doesn't seem to work this way yet. (3.19.3). Is this a coming change? The <schema>.<table> syntax restriction (the fact that you can only have <table> and not <schema>.<table> instead of triggers) is universal. It applies to both TEMP trigger and non-TEMP triggers. Only the semantic rule that the referenced <table> must be within the <same> schema is relaxed for TEMP triggers. > > > create table t (x); > insert into t values (0); > create temp view v as select * from t; > > create temp trigger trg1 instead of update on temp.v > begin > update temp.t set x = new.x; > end; > > Error: near line 5: qualified table names are not allowed on INSERT, > UPDATE, and DELETE statements within triggers > > > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
