如何创建SQLite触发器实现INSERT后自动计算列值？

针对你在SQLite中实现自动计算列触发器遇到的问题，这里给出修正方案，核心解决LAG函数无效、CASE语法错误以及变量声明不支持的问题：

问题根源 #

1. LAG函数无效：SQLite触发器的行级上下文不支持窗口函数（如LAG），无法直接通过窗口函数获取上一行数据，必须通过子查询明确查询前序记录。
2. 变量声明不支持：SQLite触发器不支持类似PostgreSQL的PL/pgSQL变量声明语法，所有值的获取和计算需直接嵌入SQL语句中。
3. CASE语法错误：之前的CASE语句可能因上下文错误或语法格式问题触发报错，需确保CASE逻辑嵌入UPDATE语句的SET子句中。

修正后的触发器代码 #

基础版本（兼容所有SQLite版本） #

CREATE TRIGGER IF NOT EXISTS compute_metrics_after_insert
AFTER INSERT ON myTable
FOR EACH ROW
BEGIN
    UPDATE myTable
    SET
        -- 存储上一行的cputil和memfree值
        util_lag = (SELECT cputil FROM myTable WHERE datetime < NEW.datetime ORDER BY datetime DESC LIMIT 1),
        mem_lag = (SELECT memfree FROM myTable WHERE datetime < NEW.datetime ORDER BY datetime DESC LIMIT 1),
        -- 计算当前行与上一行的差值
        util_diff = NEW.cputil - (SELECT cputil FROM myTable WHERE datetime < NEW.datetime ORDER BY datetime DESC LIMIT 1),
        mem_diff = NEW.memfree - (SELECT memfree FROM myTable WHERE datetime < NEW.datetime ORDER BY datetime DESC LIMIT 1),
        -- 根据util_diff设置文本描述
        util_change = CASE
            WHEN (NEW.cputil - (SELECT cputil FROM myTable WHERE datetime < NEW.datetime ORDER BY datetime DESC LIMIT 1)) > 0 THEN '上升'
            WHEN (NEW.cputil - (SELECT cputil FROM myTable WHERE datetime < NEW.datetime ORDER BY datetime DESC LIMIT 1)) < 0 THEN '下降'
            ELSE '持平'
        END
    WHERE datetime = NEW.datetime;
END;

优化版本（SQLite 3.35+，减少重复子查询） #

利用WITH子句复用前一行数据，提升效率：

CREATE TRIGGER IF NOT EXISTS compute_metrics_after_insert
AFTER INSERT ON myTable
FOR EACH ROW
BEGIN
    WITH prev_row AS (
        SELECT cputil AS prev_cputil, memfree AS prev_memfree
        FROM myTable
        WHERE datetime < NEW.datetime
        ORDER BY datetime DESC
        LIMIT 1
    )
    UPDATE myTable
    SET
        util_lag = prev_row.prev_cputil,
        mem_lag = prev_row.prev_memfree,
        util_diff = NEW.cputil - prev_row.prev_cputil,
        mem_diff = NEW.memfree - prev_row.prev_memfree,
        util_change = CASE
            WHEN prev_row.prev_cputil IS NULL THEN '无历史数据'
            WHEN NEW.cputil > prev_row.prev_cputil THEN '上升'
            WHEN NEW.cputil < prev_row.prev_cputil THEN '下降'
            ELSE '持平'
        END
    FROM prev_row
    WHERE datetime = NEW.datetime;
END;

关键说明 #

• datetime字段约束：确保datetime字段唯一且可排序，建议设置为PRIMARY KEY或添加UNIQUE约束，保证前一行数据查询的准确性。
• 第一行数据处理：优化版本中增加了对第一行数据的判断，当无历史数据时util_change会显示无历史数据，避免默认的持平描述产生歧义。
• 触发器时机：使用AFTER INSERT而非BEFORE INSERT，因为需要先插入当前行，再基于已存在的历史数据计算差值。

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