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 [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
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 <mabr...@mabrand.nl> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to