Calculate total upon inserting data in SQLite-CodePudding

I would like to add the price and amount as total upon inserting it into SQLite db. I've tried the below, but it didn't work.

INSERT INTO product (name, price, amount, total) VALUES (?, ?, ?, (price   amount))

CodePudding user response：

The classic approach is to use a view that computes the values of extra columns, and query it instead of the base table:

sqlite> CREATE TABLE raw_product(name, price, amount);
sqlite> CREATE VIEW product(name, price, amount, total) AS SELECT name, price, amount, price * amount FROM raw_product;
sqlite> INSERT INTO raw_product VALUES ('Widget', 1.50, 100);
sqlite> SELECT * FROM product;
name    price  amount  total 
------  -----  ------  -----
Widget  1.5    100     150.0

A newer way (Since Sqlite 3.31) is to use a generated column instead, which lets you include computed values in the table itself instead of wrapping it in a view:

sqlite> CREATE TABLE product(name, price, amount, total AS (price * amount));
sqlite> INSERT INTO product VALUES ('Widget', 1.50, 100);
sqlite> SELECT * FROM product;
name    price  amount  total 
------  -----  ------  -----
Widget  1.5    100     150.0

There are options you can play with to control if the value is computed each time it's requested, or if it's materialized in the database and updated when other fields of the given row are. See the linked documentation for details.