Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[David Raymond]

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:sqlite-users-boun...@mailinglists.sqlite.org] 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 [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 . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Roman Fleysher]

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 . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 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

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Mark Brand]

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
-ed database.

But it doesn't seem to work this way yet. (3.19.3). Is this a coming change?

The . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 schema is relaxed for TEMP triggers.


Right, I read too much into the second sentence.

Given the correct reading of the second sentence, I don't think the 
third sentence is quite true, depending on what  "allowed" means. The 
TEMP trigger cannot modify just any table in any attached database. 
Specifically, a table with a namefellow in less recently attached 
databases or temp will be inaccessible for modifications.


Given the fact that table names are resolved using the normal rule 
whether they are modified or just queried in the trigger, what is the 
rationale for this syntactic restriction that applies specifically to 
the modification case? As far as I can tell, this leaves the programmer 
no way to disambiguate. Is there a good workaround?


Mark
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Richard Hipp]

On 6/9/17, Mark Brand  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
>> -ed database.
>
> But it doesn't seem to work this way yet. (3.19.3). Is this a coming change?

The . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 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

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Mark Brand]

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 
-ed database. 


But it doesn't seem to work this way yet. (3.19.3). Is this a coming change?


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

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Richard Hipp]

The documentation has been updated to clarify the ambiguity and to
hopefully make it easier to understand.

On 6/9/17, Mark Brand  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
> 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