为什么在SQLite（Cloudflare D1）中，一些链式插入触发器会出现错误的插入？

• 表 arrivals 有一个插入触发器，通过 INSERT OR IGNORE 将行插入到表 places。
• 表 places 有一个唯一约束，这意味着进入 arrivals 的行数多于最终进入 places 的行数。
• 表 places 有一个插入触发器，通过 REPLACE 将行插入到 _vec_queue。
• 表 _vec_queue 没有唯一约束。

当我向 arrivals 插入110行时，在 places 中本来只有40行。可是我在 _vec_queue 中却有110行，而不是40行。这本不应该发生；如果在 places 只得到了40行，那么触发器 places 仅执行了40次，而该触发器是唯一向 _vec_queue 插入的东西。

CREATE TABLE arrivals
(
    id INTEGER NOT NULL PRIMARY KEY,
    flight_history_id TEXT NOT NULL,
    origin_airport_name TEXT NOT NULL,
    origin_airport_code TEXT NOT NULL,
    origin_city TEXT NOT NULL
);

CREATE UNIQUE INDEX unique_arr_flight_id ON arrivals (flight_history_id);

CREATE TRIGGER arrivals_log_places
AFTER insert ON arrivals
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO places (city, airport, airport_code)
    VALUES (NEW.origin_city, NEW.origin_airport_name, NEW.origin_airport_code);
END;

CREATE TABLE places
(
    id integer primary key,
    city text not null,
    airport_code text not null,
    airport text not null,
);

CREATE UNIQUE INDEX unique_place ON places (airport_code);

CREATE TRIGGER places_insert
    AFTER INSERT ON places
    FOR EACH ROW
BEGIN
    REPLACE INTO _vec_queue (item_type, item_id, action, vecIndex, embedding)
    VALUES ( 'places', NEW.id, 'INSERT', 'uk-c87-cvg-flights', 'city: ' || NEW.city || ' | airport: ' || NEW.airport || ' | airport_code: ' || NEW.airport_code );
END

CREATE TABLE _vec_queue
(
    id INTEGER PRIMARY KEY,
    item_type TEXT NOT NULL,
    item_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    vecIndex TEXT NOT NULL,
    embedding TEXT
)

CREATE UNIQUE INDEX unique_item_type_and_id_and_vec_index
    ON _vec_queue (item_type, item_id, vecIndex)

我正在使用一个单条多行插入的 REPLACE INTO 语句：

REPLACE INTO arrivals (<columns-here>)
VALUES
    (<row-1-values-here>),
    (<row-2-values-here>),
    ...
;

被编译的查询实际上是 REPLACE INTO，而不是 INSERT INTO，这似乎是造成问题的原因。

解决方案

你对 REPLACE INTO arrivals 的使用会覆盖触发器程序中的 INSERT OR IGNORE INTO places 冲突解决算法。也就是说，你本质上在触发器程序中运行了一个 INSERT OR REPLACE INTO places。

https://www.sqlite.org/lang_createtrigger.html

ON CONFLICT子句可以作为触发器主体中UPDATE或 INSERT动作的一部分来指定。然而，如果ON CONFLICT子句作为导致触发器触发的语句的一部分被指定，那么将使用外部语句的冲突处理策略。

关于同一主题的一个旧问题：触发器中的INSERT OR IGNORE