SQLite连续触发器调用问题：BMI计算后无法生成体重分类

核心问题 #

第二个触发器未生效的原因是：AFTER INSERT触发器的NEW行是插入操作刚完成时的原始数据——插入时BMI字段默认是NULL，第二个触发器的WHEN NEW.BMI > 25条件永远不成立，自然不会执行更新。

SQLite中同类型（均为AFTER INSERT）的触发器按创建顺序执行，但它们共享的是插入时的原始NEW行数据，第一个触发器对表的更新，第二个触发器在WHEN条件里无法感知到。

解决方案 #

以下三种修复方式可按需选择：

1. 合并两个触发器为一个

将计算BMI和设置体重分类的逻辑放在同一个AFTER INSERT触发器中，保证顺序执行，且直接使用计算后的BMI值：

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_and_reference
AFTER INSERT ON weight
FOR EACH ROW
BEGIN
    -- 先计算并更新BMI
    UPDATE weight
    SET BMI = (NEW.weight_in_lb / POWER(NEW.height_ft * 12 + NEW.height_in, 2)) * 703
    WHERE id = NEW.id;
    
    -- 再根据计算后的BMI设置分类
    UPDATE weight
    SET reference = 'OVERWEIGHT'
    WHERE id = NEW.id AND (NEW.weight_in_lb / POWER(NEW.height_ft * 12 + NEW.height_in, 2)) * 703 > 25;
END;

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

2. 将第二个触发器改为AFTER UPDATE

让第二个触发器监听BMI字段的更新事件，第一个触发器更新BMI后，自动触发分类设置：

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 update_reference_on_BMI_change
AFTER UPDATE OF BMI 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);

3. 直接在INSERT时计算（无需触发器）

如果不需要后续动态更新数据，直接在插入语句中计算BMI和分类，是最简洁的方案：

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
);

INSERT INTO weight(id,date_and_time,weight_in_lb,height_ft,height_in,BMI,reference)
VALUES(
    1,
    '4/20/2000',
    272,
    5,
    10,
    (272 / POWER(5*12+10,2)) *703,
    CASE WHEN (272 / POWER(5*12+10,2)) *703 >25 THEN 'OVERWEIGHT' ELSE NULL END
);

内容的提问来源于stack exchange，提问作者EnlightenedFunky