Documentation Source Text

  deleted. If the content table row cannot be found, or if it contains values
  inconsistent with the contents of the FTS index, the results can be difficult
  to predict. The FTS index may be left containing entries corresponding to the
  deleted row, which can lead to seemingly nonsensical results being returned
  by subsequent SELECT queries. The same applies when a row is updated, as
  internally an UPDATE is the same as a DELETE followed by an INSERT.

<p>
  This means that in order to keep an FTS in sync with an external content
  table, any UPDATE or DELETE operations must be applied first to the FTS
  table, and then to the external content table. For example:

<codeblock>
  CREATE TABLE t1_real(id INTEGER PRIMARY KEY, a, b, c, d);
  CREATE VIRTUAL TABLE t1_fts USING fts4(content="t1_real", b, c);

  <i>-- This works. When the row is removed from the FTS table, FTS retrieves 
  -- the row with rowid=123 and tokenizes it in order to determine the entries 
  -- that must be removed from the full-text index.
  --</i> 
  DELETE FROM t1_fts WHERE rowid = 123;
  DELETE FROM t1_real WHERE rowid = 123;

  --<i> This <b>does not work</b>. By the time the FTS table is updated, the row
  -- has already been deleted from the underlying content table. As a result
  -- FTS is unable to determine the entries to remove from the FTS index and
  -- so the index and content table are left out of sync.
  --</i>
  DELETE FROM t1_real WHERE rowid = 123;
  DELETE FROM t1_fts WHERE rowid = 123;
</codeblock>

<p>  
  Instead of writing separately to the full-text index and the content table,
  some users may wish to use database triggers to keep the full-text index
  up to date with respect to the set of documents stored in the content table.
  For example, using the tables from earlier examples:

<codeblock>