Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

Mark Brand
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

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

On 6/9/17, Mark Brand <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
> <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?


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 6/9/17, Mark Brand <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
>>> <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.

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Roman Fleysher
In reply to this post by Richard Hipp-3
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 [[hidden email]] on behalf of Richard Hipp [[hidden email]]
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] 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 [[hidden email]] on behalf of Richard Hipp [[hidden email]]
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users