SQLite视图插入时sqlite3_last_insert_rowid()不更新的问题

一、获取插入到DifficultTable的行ID #

直接调用sqlite3_last_insert_rowid()无法得到预期结果，是因为INSTEAD OF触发器内部的INSERT属于嵌套执行语句，该函数仅返回当前连接最顶层SQL语句产生的最后插入ID，不会被触发器内的操作更新。

有两种简便解决方法：

1. 使用RETURNING子句（推荐，SQLite 3.35.0+支持）
   修改SimpleInsert触发器，在插入DifficultTable时添加RETURNING id，同时插入视图时也通过该子句返回ID：

   -- 修改触发器
   CREATE TRIGGER SimpleInsert 
   INSTEAD OF INSERT ON SimpleView
   BEGIN
       INSERT INTO DifficultTable(name, fooId, barId) 
       VALUES (NEW.name,
               (SELECT id FROM FooTable WHERE name = New.foo),
               (SELECT id FROM BarTable WHERE name = New.bar)
              )
       RETURNING id;
   END;
   
   -- 插入视图并获取ID
   INSERT INTO SimpleView (name, foo, bar) VALUES ("One", "Four", "Two") RETURNING id;
   

   在C API中，执行上述INSERT语句后，通过sqlite3_step()遍历结果集即可直接获取插入的ID值。

2. 插入后查询最大ID（仅适用于无并发场景）
   若无法使用RETURNING子句，可在插入视图后执行：

   SELECT id FROM DifficultTable ORDER BY id DESC LIMIT 1;
   

   但该方法在多连接并发插入时可能获取到其他连接插入的ID，存在数据不一致风险。

二、DifficultTable的ID递增特性及查询可靠性 #

因为DifficultTable的主键使用了PRIMARY KEY(id AUTOINCREMENT)，SQLite的AUTOINCREMENT关键字会强制新生成的ID始终大于表中已存在的最大ID——即使有行被删除、或者手动插入过较大的ID值，新插入的ID仍会保持严格递增，不会复用已删除的ID。

SELECT id FROM DifficultTable ORDER BY id DESC LIMIT 1在单线程/无并发插入的场景下是可靠的，能获取到当前表中最大的ID。但如果存在多连接并发插入操作，该查询可能无法准确获取到刚插入的ID（可能被其他连接的插入操作覆盖），这种场景下优先使用RETURNING子句的方法。

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