[sqlite] trouble with TRIGGERs

[P Kishor]

I have a table

CREATE TABLE foo (
  foo_id INTEGER PRIMARY KEY,
  foo_name TEXT,
  foo_text TEXT
);

I have created a virtual table to do full text search

CREATE VIRTUAL TABLE fts_foo (
USING fts2(foo_text);

I have populated this table

INSERT INTO fts_foo (rowid, foo_text)
VALUES (foo.foo_id, foo.foo_text)

for every row in foo. So far so good. Now, I want to create TRIGGERs
that update fts_foo every time foo.foo_text is updated or a new record
is inserted into foo.

    CREATE TRIGGER update_fts
    AFTER UPDATE OF foo_text ON foo
      BEGIN
        UPDATE fts_foo
        SET foo_text = new.foo_text
        WHERE rowid = old.foo_id
      END;

    CREATE TRIGGER insert_fts
    AFTER INSERT ON foo
      BEGIN
        INSERT INTO fts_foo (rowid, foo_text)
        VALUES (new.foo_id, new.foo_text)
      END;

But I am getting a syntax error in both cases. What am I doing wrong?

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------