How to Use Trigger for Addition Between Tables in SQLite

sqlitetrigger

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.

Best Answer

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

Related Question