SQLite创建INSERT触发器调用json()函数报SET附近语法错误怎么办

你的代码无法运行有三个核心问题：

• 版本语法不兼容：SET NEW.字段名 = 值的触发器内赋值语法是SQLite 3.43.0（2023年9月发布）才新增的特性，你当前使用的3.37.2版本不支持该语法，因此会触发near "SET": syntax error报错。
• 触发器时机错误：你定义的是AFTER INSERT触发器，这个触发点在数据已经写入表之后执行，此时修改NEW变量的值不会对已存储的数据产生任何影响，哪怕语法正确也达不到转换JSON的目的。
• 语法习惯错误：:=是MySQL、PostgreSQL等数据库的赋值符号，SQLite标准赋值直接使用=即可，不需要加冒号。

根据你是否能升级SQLite版本，可选以下两种方案：

方案1：升级SQLite版本（最简便） #

将SQLite升级到3.43.0及以上版本后，使用BEFORE INSERT触发器即可实现需求，正确代码如下：

CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
    SET NEW.json = json(NEW.json);
END;

该触发器会在数据正式写入表之前，自动将传入的文本类型JSON通过内置json()函数转换为SQLite原生JSON格式存储。

方案2：适配3.37.2旧版本（无需升级） #

3.37.2版本不支持直接在普通表的BEFORE触发器中修改NEW值，可以通过「视图+INSTEAD OF触发器」的组合实现相同逻辑：

1. 保留原表结构，以常见的表结构为例：

CREATE TABLE t1 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    json TEXT -- 存储JSON内容的字段
);

2. 创建和原表结构完全一致的视图，后续所有插入操作都对该视图执行：

CREATE VIEW v_t1 AS SELECT * FROM t1;

3. 给视图创建INSTEAD OF INSERT触发器，在插入时自动完成JSON格式转换：

CREATE TRIGGER v_t1_instead_insert INSTEAD OF INSERT ON v_t1 FOR EACH ROW
BEGIN
    INSERT INTO t1 (id, json) VALUES (NEW.id, json(NEW.json));
END;

后续插入数据时直接操作视图即可，不需要手动调用json()函数，触发器会自动完成格式转换再写入真实表：

-- 插入示例
INSERT INTO v_t1 (json) VALUES ('{"name":"test","value":123}');

• SET NEW.json=json(NEW.json)：3.37.2版本无对应语法支持，版本不兼容
• SELECT NEW.json:=json(NEW.json)：SQLite不存在:=赋值操作符，SELECT语句也无法修改待插入的NEW行数据
• 仅修改为AFTER INSERT类型触发器：触发时机在数据落盘之后，修改NEW值不会改变已存储的内容

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