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
