SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE weight(id INTEGER PRIMARY KEY, date_and_time TEXT, weight_in_lb INTEGER, height_ft INTEGER, height_in INTEGER, BMI FLOAT, reference TEXT);
✓
CREATE TRIGGER insert_BMI AFTER INSERT ON weight
BEGIN
  UPDATE weight
  SET BMI = weight_in_lb * height_ft * height_in * 0.001 -- custom formula because the standard distribution of SQLite does not support math functions like POWER()
  WHERE id = NEW.id;
END;
✓
CREATE TRIGGER update_reference_overweight AFTER UPDATE ON weight
WHEN NEW.BMI > 25
BEGIN
  UPDATE weight
  SET reference = 'OVERWEIGHT'
  WHERE reference IS NOT 'OVERWEIGHT' AND id = NEW.id;
END;
✓
INSERT INTO weight(id,date_and_time,weight_in_lb,height_ft,height_in) VALUES 
  (1,'4/20/2000',272,5,10),
  (2,'4/20/2000',272,50,10);
✓
SELECT * FROM weight;


id
date_and_time
weight_in_lb
height_ft
height_in
BMI
reference


1
4/20/2000
272
5
10
13.6
null

2
4/20/2000
272
50
10
136
OVERWEIGHT