SQLite Trigger back to back invocation

Question

I have these back to back triggers that I invoking in a SQLite database. I have a table called weight that I am using to calculate BMI, and NIH Health standards classification for the weight. This is how my code looks like:

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
FOR EACH ROW
BEGIN
    UPDATE weight
    SET BMI = (NEW.weight_in_lb/POWER(NEW.height_ft * 12 + NEW.height_in,2)) * 703
    WHERE id = NEW.id;
END;
CREATE TRIGGER insert_reference_overweight
AFTER INSERT ON weight
FOR EACH ROW
WHEN NEW.BMI > 25
BEGIN
    UPDATE weight
    SET reference = 'OVERWEIGHT'
    WHERE 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);

For some reason the second trigger does not create the reference value I desire is it because the structure of the trigger does not detect the same id of 1? Or is there a way to make that trigger run after my first trigger?

I would like the overweight reference to appear in the table.

Answer 1

Your INSERT statement:

INSERT INTO weight(id,date_and_time,weight_in_lb,height_ft,height_in) VALUES (1,'4/20/2000',272,5,10);

inserts a new row with a null value for BMI.

Then, your AFTER INSERT triggers are fired, in an undefined (non-documented) order.

Let's assume that the insert_BMI trigger is fired first and sets the value of BMI.

What you expect from the insert_reference_overweight which will be fired next is to set the column reference.
But, this trigger contains this WHEN clause:

WHEN NEW.BMI > 25

and checks the original value inserted of BMI which is null and not the updated value by the first trigger.
Since null > 25 is never true, the UPDATE statement which would set the value of reference is never executed.

What you can do, is change the insert_reference_overweight trigger to an AFTER UPDATE trigger so that it is fired after the UPDATE statement of the insert_BMI trigger:

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;

Note that chaining trigger events is always tricky.

See a simplified demo.

Another solution would be the use of generated columns (requires SQLite version 3.31.0+) instead of triggers:

CREATE TABLE weight(
  id INTEGER PRIMARY KEY, 
  date_and_time TEXT, 
  weight_in_lb INTEGER, 
  height_ft INTEGER, 
  height_in INTEGER, 
  BMI FLOAT GENERATED ALWAYS AS ((weight_in_lb/POWER(height_ft * 12 + height_in,2)) * 703)
  reference TEXT GENERATED ALWAYS AS (CASE WHEN BMI > 25 THEN 'OVERWEIGHT' END)
);

See the demo.