On 10/27/2015 05:06 PM, chromedout64 at yahoo.com wrote: > Using the example from the documentation at https://www.sqlite.org/fts5.html > I thought this worked in a previous version. > > CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c); > CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', > content_rowid='a'); > CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN > INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); > END; > CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN > INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, > old.c); > END; > CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN > INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, > old.c); > INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); > END; > > REPLACE INTO tbl VALUES(1,'foo','bar');
I think this may have worked with an older version of FTS5 that did not support REPLACE. The trouble is that because the top-level statement uses OR REPLACE conflict handling, so do the INSERT statements fired by the triggers. And OR REPLACE conflict handling was broken for external content tables. Fts5 is now updated on trunk so that OR REPLACE is ignored for contentless and external content tables: http://sqlite.org/src/info/a85c2a4758c27e8d So this sort of thing should work again. Dan.
