Re: Trigger name missing

On 26/02/2020 12:18, Richard Hipp wrote:
> On 2/26/20, Jean-Luc Hainaut <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users