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