While working on Turso, I found this undocumented behaviour which I think should either be documented or modified: NEW/OLD columns in triggers don't have an affinity. This applies to columns in WHEN, WHERE, and HAVING clauses. Verified on SQLite 3.53.0.

Example:

CREATE TABLE t(a TEXT);                                                                                       
CREATE TABLE log(msg);

CREATE TRIGGER tr_when AFTER INSERT ON t WHEN NEW.a = 5   
  BEGIN INSERT INTO log VALUES('when_fired'); END;

CREATE TRIGGER tr_where AFTER INSERT ON t                                                                     
  BEGIN INSERT INTO log SELECT 'body_where_fired' WHERE NEW.a = 5; END;

CREATE TRIGGER tr_having AFTER INSERT ON t                                                                    
  BEGIN                                                   
    INSERT INTO log SELECT 'body_having_fired'
    FROM (SELECT 1 AS x) GROUP BY x HAVING NEW.a = 5;
  END;

INSERT INTO t VALUES('5');

SELECT 'where_match', count(*) FROM t WHERE a = 5
UNION ALL
SELECT 'when_fired', count(*) FROM log WHERE msg = 'when_fired'                                    
UNION ALL
SELECT 'body_where_fired', count(*) FROM log WHERE msg = 'body_where_fired'                              
UNION ALL                                                                                                     
SELECT 'body_having_fired', count(*) FROM log WHERE msg = 'body_having_fired';

SQLite outputs:

where_match|1                                                                                                 
when_fired|0                                              
body_where_fired|0
body_having_fired|0

However, rowids and INTEGER PRIMARY KEY columns have an INTEGER affinity. The snippet below shows this for IPK's.

CREATE TABLE t(id INTEGER PRIMARY KEY, n INTEGER, s TEXT);                                                  
CREATE TABLE log(probe TEXT);                                                                                 
                                                                                                            
CREATE TRIGGER tr_id AFTER INSERT ON t WHEN NEW.id = '1'                                                    
  BEGIN INSERT INTO log VALUES('ipk_fired'); END;                                                              
                                                                                                            
INSERT INTO t(id, n, s) VALUES(1, 1, 'x');                                                                    
                                                                                                              
SELECT 'ipk_fired', count(*) FROM log WHERE probe = 'ipk_fired';                                     

SQLite outputs:

ipk_fired|1