How to maintain trees and their roots in SQLite
Finding all nodes in a subtree to update them requires a recursive common table expression:

-- update the root of all nodes in 'a's subtree to 'x'
WITH RECURSIVE subtree(id) AS (
    VALUES('a')
    UNION ALL
    SELECT Node.id
    FROM Node
    JOIN subtree ON Node.parent = subtree.id
)
UPDATE Node
SET root = 'x'
WHERE id IN subtree;


In SQLite, CTEs are not allowed in triggers.