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
