Incomplete SQL error in SQLite database

Question

I've a SQLite script which initializes a DB (SQLite 3.8.2), created as follows:

sqlite3 -init database_creation.sql sample.db

The script file is like:

 // Some `CREATE TABLE` statements

 CREATE TRIGGER update_totalprice AFTER UPDATE
   OF price, amount, boxes ON product
   BEGIN UPDATE product SET totalprice = price * boxes * amount
   WHERE idproduct = NEW.idproduct; END

 // Some `INSERT` statements.

But it generates the following error:

Error: incomplete SQL: CREATE TRIGGER update_totalprice AFTER UPDATE
   OF price, amount, boxes ON product
   BEGIN UPDATE product SET totalprice = price * boxes * amount
   WHERE idproduct = NEW.idproduct; END

// The rest of the file to the end.

I'm sure there're syntax errors nor creating the tables neither inserting the test rows, because if I comment the CREATE TRIGGER statement all works perfectly fine, and I don't find the syntax error either.

ABu · Accepted Answer · 2015-09-17 14:54:39Z

2

Ok, the problem was pretty simple. I though the "END" keyword was enough to mark the end of the CREATE TRIGGER statement (even more knowing that the UPDATE statement has already its own ';'), but after END, another ; is required:

 CREATE TRIGGER update_totalprice
 AFTER UPDATE OF price, amount, boxes ON product
 BEGIN
   UPDATE product SET totalprice = price * boxes * amount
   WHERE idproduct = NEW.idproduct;
 END;

edited Sep 17, 2015 at 14:54

answered Mar 14, 2015 at 18:19

ABu

12.5k8 gold badges62 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Incomplete SQL error in SQLite database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related