By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE city (id INT, countrycode INT, population INT);
CREATE TABLE country (code INT, population INT);
CREATE TRIGGER diff_pop_ai
AFTER INSERT
ON city
FOR EACH ROW
UPDATE country
SET population = population + NEW.population
WHERE country.code = NEW.countrycode;
CREATE TRIGGER diff_pop_au
AFTER UPDATE
ON city
FOR EACH ROW
UPDATE country
SET population = population - OLD.population + NEW.population
WHERE country.code = NEW.countrycode;
CREATE TRIGGER diff_pop_ad
AFTER DELETE
ON city
FOR EACH ROW
UPDATE country
SET population = population - OLD.population
WHERE country.code = OLD.countrycode;
INSERT INTO country VALUES (1,0), (2,0);
Records: 2 Duplicates: 0 Warnings: 0
INSERT INTO city VALUES (1,1,100);
INSERT INTO city VALUES (2,2,200);
INSERT INTO city VALUES (3,1,300);
SELECT * FROM city;
SELECT * FROM country;
| id | countrycode | population |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 200 |
| 3 | 1 | 300 |
| code | population |
|---|---|
| 1 | 400 |
| 2 | 200 |
UPDATE city SET population = 400 WHERE id = 2;
UPDATE city SET population = 500 WHERE id = 3;
SELECT * FROM city;
SELECT * FROM country;
Rows matched: 1 Changed: 1 Warnings: 0
Rows matched: 1 Changed: 1 Warnings: 0
| id | countrycode | population |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 400 |
| 3 | 1 | 500 |
| code | population |
|---|---|
| 1 | 600 |
| 2 | 400 |
DELETE FROM city WHERE id = 3;
SELECT * FROM city;
SELECT * FROM country;
| id | countrycode | population |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 400 |
| code | population |
|---|---|
| 1 | 100 |
| 2 | 400 |