Trigger name missing

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger name missing

Jean-Luc Hainaut
Hi all,

It seems that SQLite (version 31.1) accepts a trigger declaration in
which the name is missing. When fired, this trigger doesn't crashes but
exhibits a strange behaviour. In particular, while expression
"new.<colName>" in an "insert" trigger returns the correct value, the
equivalent expression "select <colName> from T where Id = new.Id" always
returns null (column "Id" is the PK of table "T"). Similarly, "update T
set <columnName> = <expression>  where Id = new.Id" (silently) fails.

Not critical but annoying if you are, like me, absent minded when typing
code!

Regards

Jean-Luc Hainaut

_______________________________________________
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: Trigger name missing

Richard Hipp-3
On 2/26/20, Jean-Luc Hainaut <[hidden email]> wrote:

> Hi all,
>
> It seems that SQLite (version 31.1) accepts a trigger declaration in
> which the name is missing. When fired, this trigger doesn't crashes but
> exhibits a strange behaviour. In particular, while expression
> "new.<colName>" in an "insert" trigger returns the correct value, the
> equivalent expression "select <colName> from T where Id = new.Id" always
> returns null (column "Id" is the PK of table "T"). Similarly, "update T
> set <columnName> = <expression>  where Id = new.Id" (silently) fails.
>

What is the text of your trigger?
--
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: Trigger name missing

Dan Kennedy-4
In reply to this post by Jean-Luc Hainaut

On 26/2/63 16:31, Jean-Luc Hainaut wrote:

> Hi all,
>
> It seems that SQLite (version 31.1) accepts a trigger declaration in
> which the name is missing. When fired, this trigger doesn't crashes
> but exhibits a strange behaviour. In particular, while expression
> "new.<colName>" in an "insert" trigger returns the correct value, the
> equivalent expression "select <colName> from T where Id = new.Id"
> always returns null (column "Id" is the PK of table "T"). Similarly,
> "update T set <columnName> = <expression>  where Id = new.Id"
> (silently) fails.


A statement like the following creates a "BEFORE" trigger named "AFTER".
Does that explain things?

   CREATE TRIGGER AFTER INSERT ON t1 BEGIN
     ...
   END;

I find I fall into this trap about once every 18 months...

Dan.



>
> Not critical but annoying if you are, like me, absent minded when
> typing code!
>
> Regards
>
> Jean-Luc Hainaut
>
> _______________________________________________
> 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: Trigger name missing

Simon Slavin-3
On 26 Feb 2020, at 2:15pm, Dan Kennedy <[hidden email]> wrote:

> A statement like the following creates a "BEFORE" trigger named "AFTER". Does that explain things?
>
>   CREATE TRIGGER AFTER INSERT ON t1 BEGIN
>     ...
>   END;
>
> I find I fall into this trap about once every 18 months...

If only you knew someone with the power to make this generate SQLITE_MISUSE so that you, and other programmers, don't have this problem.  Oh well.

(Backward compatibility ?  Do you think anyone who used the word AFTER really wants a BEFORE trigger ?  More likely to be a bug they should know about.)
_______________________________________________
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: Trigger name missing

Richard Hipp-3
On 2/26/20, Simon Slavin <[hidden email]> wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    ...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
    ...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
--
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: Trigger name missing

Jose Isaias Cabrera-4

SQLite is even better than I thought...

________________________________
From: sqlite-users <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Wednesday, February 26, 2020 11:44 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Trigger name missing

On 2/26/20, Simon Slavin <[hidden email]> wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    ...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
    ...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
--
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: Trigger name missing

Jean-Luc Hainaut
In reply to this post by Richard Hipp-3
On 26/02/2020 12:18, Richard Hipp wrote:

> On 2/26/20, Jean-Luc Hainaut <[hidden email]> wrote:
>> Hi all,
>>
>> It seems that SQLite (version 31.1) accepts a trigger declaration in
>> which the name is missing. When fired, this trigger doesn't crashes but
>> exhibits a strange behaviour. In particular, while expression
>> "new.<colName>" in an "insert" trigger returns the correct value, the
>> equivalent expression "select <colName> from T where Id = new.Id" always
>> returns null (column "Id" is the PK of table "T"). Similarly, "update T
>> set <columnName> = <expression>  where Id = new.Id" (silently) fails.
>>
> What is the text of your trigger?

This trigger belongs to a small experimental application I'm writting to
study the extent to what application code (initially in Java, Python,
etc.) can be integrated into SQL, notably through triggers. In short,
can one convert a standard 3-tier business application into just a GUI +
an active database, without the standard application program between them?
The following trigger controls the registration of a customer order
[insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when
the available quantity (Qavail) of the requested item is sufficient.
If the name 'CORD_INS1' is missing, this trigger (among others):
   - updates the ITEM table. [successfully]
   - completes the customer order (Price and State in CUSTORDER). [fails]
   - creates an invoice (in CUSTINVOICE) and prints it in a text file.
[successfully]

After reading all your explanations and comments, my interpretation is
as follows:
1. The SQLite syntax tells me that the "before/after/instead of" keyword
can be missing, in which case (I guess) "before" is assumed.
2. So, my "name-less" trigger is valid and must be read:
    create trigger "after" before insert on CUSTORDER ...
3. In a "before" trigger, the current row cannot be updated, since it
doesn't exist yet (though several RDBMS have a specific syntax for that).
4. This explains why SQLite legitimely ignores the second update.
Am I right?
If I am, this behaviour is "not a bug but a feature". It could be useful
to precise these facts in the documentation.

Thanks to all

Jean-Luc Hainaut

create table CUSTOMER (CustID,Name,Address,City,Account,...);
create table ITEM
(ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);
create table CUSTORDER (OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
create table CUSTINVOICE
(InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);
create table SUPPLIER (SuppID,Name,City,...);
create table OFFER (SuppID,ItemID,Price,Delay,...);
create table SUPPORDER (OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);

create trigger CORD_INS1
after insert on CUSTORDER
for each row
when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
begin
    --
    -- Subtract Qty from Qavail:
    update ITEM
    set   Qavail = Qavail - new.Qty
    where ItemID = new.ItemID;
    --
    --...
    -- Set CUSTORDER.State to 'invoiced' or 'pending'
    update CUSTORDER
    set   Price = (select Price from ITEM where ItemID = new.ItemID),
          State = case when new.Qty <= (select QonHand from ITEM where
ItemID = new.ItemID)
                       then 'invoiced'
                       else 'pending'
                  end
    where OrdID = new.OrdID;
    --
    -- Create an invoice and print it:
    insert into CUSTINVOICE(...);
    --
end;

_______________________________________________
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: Trigger name missing

John Gillespie-2
Would it be possible to create an SQL verification program, which just like
'sqlite3_analyzer' and 'sqldiff' could be run separately?
It could *warn* about apparently incompletely defined triggers and other
possible pitfalls.

Then developers could use it before installing the next version of SQLite3.

John G

On Wed, 26 Feb 2020 at 19:09, Jean-Luc Hainaut <[hidden email]>
wrote:

> On 26/02/2020 12:18, Richard Hipp wrote:
> > On 2/26/20, Jean-Luc Hainaut <[hidden email]> wrote:
> >> Hi all,
> >>
> >> It seems that SQLite (version 31.1) accepts a trigger declaration in
> >> which the name is missing. When fired, this trigger doesn't crashes but
> >> exhibits a strange behaviour. In particular, while expression
> >> "new.<colName>" in an "insert" trigger returns the correct value, the
> >> equivalent expression "select <colName> from T where Id = new.Id" always
> >> returns null (column "Id" is the PK of table "T"). Similarly, "update T
> >> set <columnName> = <expression>  where Id = new.Id" (silently) fails.
> >>
> > What is the text of your trigger?
>
> This trigger belongs to a small experimental application I'm writting to
> study the extent to what application code (initially in Java, Python,
> etc.) can be integrated into SQL, notably through triggers. In short,
> can one convert a standard 3-tier business application into just a GUI +
> an active database, without the standard application program between them?
> The following trigger controls the registration of a customer order
> [insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when
> the available quantity (Qavail) of the requested item is sufficient.
> If the name 'CORD_INS1' is missing, this trigger (among others):
>    - updates the ITEM table. [successfully]
>    - completes the customer order (Price and State in CUSTORDER). [fails]
>    - creates an invoice (in CUSTINVOICE) and prints it in a text file.
> [successfully]
>
> After reading all your explanations and comments, my interpretation is
> as follows:
> 1. The SQLite syntax tells me that the "before/after/instead of" keyword
> can be missing, in which case (I guess) "before" is assumed.
> 2. So, my "name-less" trigger is valid and must be read:
>     create trigger "after" before insert on CUSTORDER ...
> 3. In a "before" trigger, the current row cannot be updated, since it
> doesn't exist yet (though several RDBMS have a specific syntax for that).
> 4. This explains why SQLite legitimely ignores the second update.
> Am I right?
> If I am, this behaviour is "not a bug but a feature". It could be useful
> to precise these facts in the documentation.
>
> Thanks to all
>
> Jean-Luc Hainaut
>
> create table CUSTOMER (CustID,Name,Address,City,Account,...);
> create table ITEM
> (ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);
> create table CUSTORDER (OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
> create table CUSTINVOICE
> (InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);
> create table SUPPLIER (SuppID,Name,City,...);
> create table OFFER (SuppID,ItemID,Price,Delay,...);
> create table SUPPORDER (OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);
>
> create trigger CORD_INS1
> after insert on CUSTORDER
> for each row
> when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
> and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
> begin
>     --
>     -- Subtract Qty from Qavail:
>     update ITEM
>     set   Qavail = Qavail - new.Qty
>     where ItemID = new.ItemID;
>     --
>     --...
>     -- Set CUSTORDER.State to 'invoiced' or 'pending'
>     update CUSTORDER
>     set   Price = (select Price from ITEM where ItemID = new.ItemID),
>           State = case when new.Qty <= (select QonHand from ITEM where
> ItemID = new.ItemID)
>                        then 'invoiced'
>                        else 'pending'
>                   end
>     where OrdID = new.OrdID;
>     --
>     -- Create an invoice and print it:
>     insert into CUSTINVOICE(...);
>     --
> end;
>
> _______________________________________________
> 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: Trigger name missing

Simon Slavin-3
On 7 Mar 2020, at 4:49pm, John G <[hidden email]> wrote:

> Would it be possible to create an SQL verification program, which just like 'sqlite3_analyzer' and 'sqldiff' could be run separately?
> It could *warn* about apparently incompletely defined triggers and other possible pitfalls.

The shell tool has this ability, by executing the following PRAGMAs:

<https://sqlite.org/pragma.html#pragma_cell_size_check>
<https://sqlite.org/pragma.html#pragma_integrity_check>
<https://sqlite.org/pragma.html#pragma_foreign_key_check>

Used in the above order it will spot several kinds of corruption.  It can be scripted from your OS command-line shell:

<https://sqlite.org/cli.html#using_sqlite3_in_a_shell_script>
_______________________________________________
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: Trigger name missing

Keith Medcalf
In reply to this post by John Gillespie-2

You mean like take a "boo" at the defined triggers?

select name, tbl_name, sql from sqlite_master where type='trigger';

would pretty much make clear that the defined trigger is not what you thought it was ...

create table x(x);
create trigger after insert on x begin select 1; end;

select name, tbl_name, sql from sqlite_master where type='trigger';
after|x|CREATE TRIGGER after insert on x begin select 1; end

seems pretty clear that the sql statement creates a trigger called "after" on a table called "x" and that the after was parsed as the trigger name.


--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of John G
>Sent: Saturday, 7 March, 2020 09:49
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Trigger name missing
>
>Would it be possible to create an SQL verification program, which just
>like
>'sqlite3_analyzer' and 'sqldiff' could be run separately?
>It could *warn* about apparently incompletely defined triggers and other
>possible pitfalls.
>
>Then developers could use it before installing the next version of
>SQLite3.
>
>John G
>
>On Wed, 26 Feb 2020 at 19:09, Jean-Luc Hainaut <jean-
>[hidden email]>
>wrote:
>
>> On 26/02/2020 12:18, Richard Hipp wrote:
>> > On 2/26/20, Jean-Luc Hainaut <[hidden email]> wrote:
>> >> Hi all,
>> >>
>> >> It seems that SQLite (version 31.1) accepts a trigger declaration in
>> >> which the name is missing. When fired, this trigger doesn't crashes
>but
>> >> exhibits a strange behaviour. In particular, while expression
>> >> "new.<colName>" in an "insert" trigger returns the correct value,
>the
>> >> equivalent expression "select <colName> from T where Id = new.Id"
>always
>> >> returns null (column "Id" is the PK of table "T"). Similarly,
>"update T
>> >> set <columnName> = <expression>  where Id = new.Id" (silently)
>fails.
>> >>
>> > What is the text of your trigger?
>>
>> This trigger belongs to a small experimental application I'm writting
>to
>> study the extent to what application code (initially in Java, Python,
>> etc.) can be integrated into SQL, notably through triggers. In short,
>> can one convert a standard 3-tier business application into just a GUI
>+
>> an active database, without the standard application program between
>them?
>> The following trigger controls the registration of a customer order
>> [insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when
>> the available quantity (Qavail) of the requested item is sufficient.
>> If the name 'CORD_INS1' is missing, this trigger (among others):
>>    - updates the ITEM table. [successfully]
>>    - completes the customer order (Price and State in CUSTORDER).
>[fails]
>>    - creates an invoice (in CUSTINVOICE) and prints it in a text file.
>> [successfully]
>>
>> After reading all your explanations and comments, my interpretation is
>> as follows:
>> 1. The SQLite syntax tells me that the "before/after/instead of"
>keyword
>> can be missing, in which case (I guess) "before" is assumed.
>> 2. So, my "name-less" trigger is valid and must be read:
>>     create trigger "after" before insert on CUSTORDER ...
>> 3. In a "before" trigger, the current row cannot be updated, since it
>> doesn't exist yet (though several RDBMS have a specific syntax for
>that).
>> 4. This explains why SQLite legitimely ignores the second update.
>> Am I right?
>> If I am, this behaviour is "not a bug but a feature". It could be
>useful
>> to precise these facts in the documentation.
>>
>> Thanks to all
>>
>> Jean-Luc Hainaut
>>
>> create table CUSTOMER (CustID,Name,Address,City,Account,...);
>> create table ITEM
>> (ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);
>> create table CUSTORDER
>(OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
>> create table CUSTINVOICE
>> (InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);
>> create table SUPPLIER (SuppID,Name,City,...);
>> create table OFFER (SuppID,ItemID,Price,Delay,...);
>> create table SUPPORDER
>(OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);
>>
>> create trigger CORD_INS1
>> after insert on CUSTORDER
>> for each row
>> when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
>> and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
>> begin
>>     --
>>     -- Subtract Qty from Qavail:
>>     update ITEM
>>     set   Qavail = Qavail - new.Qty
>>     where ItemID = new.ItemID;
>>     --
>>     --...
>>     -- Set CUSTORDER.State to 'invoiced' or 'pending'
>>     update CUSTORDER
>>     set   Price = (select Price from ITEM where ItemID = new.ItemID),
>>           State = case when new.Qty <= (select QonHand from ITEM where
>> ItemID = new.ItemID)
>>                        then 'invoiced'
>>                        else 'pending'
>>                   end
>>     where OrdID = new.OrdID;
>>     --
>>     -- Create an invoice and print it:
>>     insert into CUSTINVOICE(...);
>>     --
>> end;
>>
>> _______________________________________________
>> 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