SQLite FTS5表删除触发器触发5值对应4列运行错误求助

1. 直接触发报错的原因：删除、更新触发器中的INSERT语句，指定的目标列只有4个，但是VALUES子句传入了5个参数，参数数量和列数量不匹配，因此抛出sqlite3.OperationalError: 5 values for 4 columns错误。
2. 底层逻辑错误：你对FTS5的控制类INSERT语法理解错误。FTS5支持通过INSERT语句传入'delete'参数实现记录删除，但要求将FTS表本身的名称作为特殊隐藏列放在列列表的第一位，'delete'是传给这个隐藏列的控制参数，而非你自定义的src_row列的取值。你没有在列列表中声明这个隐藏列，直接把'delete'放到了参数里，就导致了参数多一个的问题。

有两种常用修复方式，任选一种即可：

方案1：改用普通DELETE语句实现同步（写法更直观，不易出错） #

直接匹配要删除的FTS表记录执行DELETE操作，不需要使用特殊的INSERT控制语法，修改后的触发器代码如下：

-- 插入触发器保持原有逻辑即可，注意替换掉代码中的占位符
CREATE TRIGGER Jacks_after_insert AFTER INSERT ON Jacks
BEGIN
    INSERT INTO searchTable (src_row, manufacturer_part_number, vendor_part_number, src_table)
    VALUES (new.id, new.Manufacturer_Part_Number, new.Vendor_Part_Number, "Jacks");
END;

-- 删除触发器改用DELETE语法
CREATE TRIGGER Jacks_after_delete AFTER DELETE ON Jacks
BEGIN
    DELETE FROM searchTable 
    WHERE src_row = old.id AND src_table = "Jacks";
END;

-- 更新触发器先删旧记录再插新记录
CREATE TRIGGER Jacks_after_update AFTER UPDATE ON Jacks
BEGIN
    DELETE FROM searchTable 
    WHERE src_row = old.id AND src_table = "Jacks";
    INSERT INTO searchTable (src_row, manufacturer_part_number, vendor_part_number, src_table)
    VALUES (new.id, new.Manufacturer_Part_Number, new.Vendor_Part_Number, "Jacks");
END;

方案2：补全FTS5隐藏列，使用原有INSERT控制语法 #

如果要保留你原有使用'delete'参数的写法，只需要在列列表第一位加上FTS表名对应的隐藏列即可：

CREATE TRIGGER Jacks_after_delete AFTER DELETE ON Jacks
BEGIN
    -- 列列表第一位加上隐藏列searchTable，对应参数的'delete'
    INSERT INTO searchTable(searchTable, src_row, manufacturer_part_number, vendor_part_number, src_table)
    VALUES ('delete', old.id, old.manufacturer_part_number, old.vendor_part_number, "Jacks");
END;

-- 更新触发器的第一个DELETE控制INSERT也要同步补全隐藏列
CREATE TRIGGER Jacks_after_update AFTER UPDATE ON Jacks
BEGIN
    INSERT INTO searchTable(searchTable, src_row, manufacturer_part_number, vendor_part_number, src_table)
    VALUES ('delete', old.id, old.manufacturer_part_number, old.vendor_part_number, "Jacks");
    INSERT INTO searchTable (src_row, manufacturer_part_number, vendor_part_number, src_table)
    VALUES (new.id, new.Manufacturer_Part_Number, new.Vendor_Part_Number, "Jacks");
END;

你代码中的{manufacturer_part_number}、{search_table}属于模板占位符，实际执行SQL前要替换为真实的字段名、表名，避免语法错误。如果有100张业务表，可以通过Python遍历所有表名批量生成触发器，无需手动编写减少出错概率。

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