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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of John G >Sent: Saturday, 7 March, 2020 09:49 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >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- >luc.hain...@unamur.be> >wrote: > >> On 26/02/2020 12:18, Richard Hipp wrote: >> > On 2/26/20, Jean-Luc Hainaut <jean-luc.hain...@unamur.be> 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 >> 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
