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
