CODEWITHSUNDEEP
sqlitetriggers
dorachan2010
dorachan2010

Reputation: 1091

sqlite how to update a column automatically from a variable

I am trying to see if trigger is the function I can use for my situation. I basically want to imitate automatic update feature of Excel like how Sum(A:A) automatically updates and returns the sum. Here is my schema.

.schema
CREATE TABLE product (id integer, name text, price integer);
CREATE TABLE sum(total integer);

I want to trigger an INSERT/UPDATE command that sums price and inserts the value to total. I have tried the following but syntax error is giving me a headache.

CREATE TRIGGER strigger insert on product
begin
INSERT into sum(total) values(sum(price) from product);
end;

Is this the right approach? or Do I have to deliberately call an update command from sqlite console or host language? Thanks for your help.

Upvotes: 0

Views: 1185

Answers (1)

CL.
CL.

Reputation: 180040

It would be possible to write a trigger:

CREATE TRIGGER strigger
AFTER INSERT ON product
BEGIN
    UPDATE sum SET total = (SELECT sum(price) FROM product);
END;

But in many cases, it would be a better idea to compute the sum on demand:

CREATE VIEW sum AS
SELECT sum(price) AS total FROM product;

Upvotes: 1

Related Questions