How to use trigger to perform addition from one table columns into another table column where id matches Sqlite

Question

Below is my SQLite Database with two tables.

import sqlite3
conn=sqlite3.connect('calc.db')
conn.execute("""CREATE TABLE IF NOT EXISTS sum
 (id TEXT UNIQUE,
 name TEXT,
 total TEXT 
 )""")
conn.execute("""CREATE TABLE IF NOT EXISTS newtable
 (id TEXT,
 name TEXT,
 num TEXT 
 )""")
conn.execute("""INSERT INTO sum(id, name, total) \
 VALUES('001', 'name1', '')""")
conn.execute("""INSERT INTO sum(id, name, total) \
 VALUES('002', 'name2', '')""")
#
conn.execute("""INSERT INTO newtable(id, name, num) \
 VALUES('001', 'name1', '1000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
 VALUES('002', 'name2', '2000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
 VALUES('001', 'name1', '4000')""")
conn.commit()
conn.close()

Sum table has unique id and newtable id is not unique. I want to perform addition in sum table in total column that should be taken the addition value from newtable column when inserting and if id matches. Hoe to do. My columns are text type. If not posible in TEXT type i can change it into integer but it is better to continue in TEXT type if possible. How to do.

Answer 1

You can add a trigger to your python script

CREATE TABLE IF NOT EXISTS sum
 (id TEXT UNIQUE,
 name TEXT,
 total TEXT 
 )

CREATE TABLE IF NOT EXISTS newtable
 (id TEXT,
 name TEXT,
 num TEXT 
 )

INSERT INTO sum(id, name, total) 
 VALUES('001', 'name1', '');

INSERT INTO sum(id, name, total) 
 VALUES('002', 'name2', '');

CREATE TRIGGER sum_after_insert
 AFTER INSERT ON newtable
BEGIN
 UPDATE sum SET total = total + NEW.num WHERE id = NEW.id;
END;

INSERT INTO newtable(id, name, num) 
 VALUES('001', 'name1', '1000');

INSERT INTO newtable(id, name, num) 
 VALUES('002', 'name2', '2000');

INSERT INTO newtable(id, name, num) 
 VALUES('001', 'name1', '4000');

SELECT * FROM sum;

id | name | total
:-- | :---- | :----
001 | name1 | 5000 
002 | name2 | 2000 

db<>fiddle here

• Mano: Its working perfect for me. Thank you

Answer 2

CREATE TRIGGER 
AFTER INSERT
ON newtable
FOR EACH ROW
BEGIN
 UPDATE sum 
 SET total = total + NEW.num
 WHERE name = NEW.name;
END

DEMO

• Mano: The name column is not unique. If i change the name to id in WHERE clause its working perfect for me. Thank you.