Conditional Trigger using WHEN clause NOT Working.

(1) By anonymous on 2022-02-23 08:03:24 [source]

I am trying the following set of simple SQL queries,

************************************************
CREATE TABLE BD (A integer, B TEXT);
CREATE VIRTUAL TABLE BDFTS USING FTS4 (CONTENT="BD", A, B);
	
CREATE TRIGGER BDFTSI AFTER INSERT ON BD 
WHEN NEW.A > 1
BEGIN
	INSERT INTO BDFTS(DOCID, A, B) VALUES (NEW.ROWID, NEW.A, NEW.B);
END;
	
INSERT INTO BD VALUES(1, '000');
INSERT INTO BD VALUES(1, '111');
INSERT INTO BD VALUES(2, '222');
INSERT INTO BD VALUES(3, '333');
	
SELECT * 
FROM bdfts
************************************************

However, bdfts stores all the rows (i.e. 4 entries in the above case) being inserted to bd without caring about the WHEN clause. 

What might be wrong in this? 
Thanks.

(2) By Harald Hanche-Olsen (hanche) on 2022-02-23 09:06:16 in reply to 1 [link] [source]

As always, it would be helpful if you were to tell us what version of sqlite you're on.

For what it's worth (probably not much), I am on the latest version (from the repo yesterday). I haven't got FTS4 compiled, so I just replaced BDTFS by a regular table, and your trigger does what is expected of it in that case.

(3) By anonymous on 2022-02-23 09:14:57 in reply to 2 [link] [source]

Hi @hanche,

My SQLite version is 3.24.0

I tried the trigger with a normal table, and it is working correctly as you also mentioned.

However, as per my requirements, I need to use it for full text search, and therefore have a need for FTS4, in which case, the trigger is failing.

(4) By Dan Kennedy (dan) on 2022-02-23 11:16:09 in reply to 1 [link] [source]

"bdfts" is an external content fts4 table. This means that when you try to extract rows from the table, it reads them from the content table - in this case "bd" - instead:

https://sqlite.org/fts3.html#the_content_option_

So the query "SELECT * FROM bdfts" will return the same set of rows regardless of whether or not the trigger exists or ran.

If you run a full text query against "bdfts", the results will reflect only those rows added to the full text index by your trigger.