SQLite CREATE TRIGGER ... SQLite error 1 with statement: incomplete input

Question

trying to create a trigger in Xcode / Swift5 / GRDB. the trigger in the codeblock below fails silently, so I move it and see the error in the title [I have removed the text of the SQL statement from the title]. the weird part is that DB Browser will run the SQL and CREATE the trigger just fine. and the app responds as expected with the externally created trigger. and the UPDATE within seems to work fine outside of the trigger.

opened an issue with the GRDB github repo...just passing the raw SQL thru. check several validators online. all either said there was nothing wrong, or failed on CREATE TABLE in the block below...so no faith in any of the validators.

remembered I have DraftCode on the ipad, with phpLiteAdmin included. send the SQL over and get the same incomplete input error. move back to the desktop...update PHP, and get phpLiteAdmin...same error.

so...it's either a SQLite bug like https://github.com/sqlitebrowser/sqlitebrowser/issues/1470? or more likely a syntax error on my part. I've never played with triggers before...so I may be the dumbass overlooking the obvious. if so, please help me rectify.

one more bit of interesting... in phpLiteAdmin, using the trigger builder, everything works just fine. but trying to run the resulting create SQL statement, copied/delete trigger/and pasted back in, generates the same error

SQLite version: 3.31.1
PHP version: 7.3.14
phpLiteAdmin version: 1.9.8.2

any help with creating the trigger in either GRDB or phpLiteAdmin as a SQL statement would be appreciated. thnx.

 CREATE TABLE CartItem (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 transactionID INTEGER DEFAULT NULL,
 wasReceived BOOLEAN,
 sku VARCHAR,
 label VARCHAR,
 description VARCHAR,
 unitLabel VARCHAR,
 cost DOUBLE,
 wholesale DOUBLE,
 retail DOUBLE,
 tax DOUBLE,
 cartQty FLOAT,
 modified REAL
 );

 -- because 'transaction' is reserved
 CREATE TABLE TransXion (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 transactionNum VARCHAR,
 shippingSubtotal DOUBLE,
 taxableSubtotal DOUBLE,
 nontaxableSubtotal DOUBLE,
 subtotal DOUBLE,
 total DOUBLE,
 paymentsSubtotal DOUBLE,
 balance DOUBLE,
 itemsWereRcvd BOOLEAN,
 locked BOOLEAN,
 modified REAL
 );


 CREATE TRIGGER insert_TransXion_Dollar_Values_trigger
     AFTER INSERT ON CartItem
     WHEN transactionID = new.transactionID
 BEGIN
     UPDATE TransXion
         SET total = (
             SELECT SUM(ci.retail) AS retail_total
             FROM CartItem ci
             WHERE ci.transactionID = TransXion.id
             )
         WHERE id IN (
             SELECT transactionID
             FROM CartItem ci
             WHERE ci.transactionID = TransXion.id
             );
 END;

score 1 · Answer 1 · answered Feb 09 '20 at 20:01

In phpLiteAdmin's SQL tab, statements are separated by the delimiter that you can configure below the input tab. By default, this is ;. In your trigger definition you use ;, but splitting what you enter at this point results in those two queries:

First query:

CREATE TRIGGER insert_TransXion_Dollar_Values_trigger
 AFTER INSERT ON CartItem
 WHEN transactionID = new.transactionID
 BEGIN
 UPDATE TransXion
     SET total = (
         SELECT SUM(ci.retail) AS retail_total
         FROM CartItem ci
         WHERE ci.transactionID = TransXion.id
         )
     WHERE id IN (
         SELECT transactionID
         FROM CartItem ci
         WHERE ci.transactionID = TransXion.id
         );

second query:

END;

So this obviously fails. You can simply configure another delimiter like $ to make sure it executes as one query.

SQLite CREATE TRIGGER ... SQLite error 1 with statement: incomplete input

1 Answers1