sqlitetriggerssql-updatesql-insertgenerated-columns

SQLite Trigger back to back invocation


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.


Solution

  • 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.