I need to store a tree in SQLite. I have found a very good method for SQL Server here:
Tree structures in ASP.NET and SQL Server
The concept with triggers is clear end forward thinking.
I try to translate the described triggers to SQLite
SQL Server Version:
CREATE TRIGGER dfTree_UpdateTrigger
ON dfTree
FOR UPDATE AS
-- if we've modified the parentId, then we
-- need to do some calculations
IF UPDATE (parentId)
UPDATE child
-- to calculate the correct depth of a node, remember that
-- - old.depth is the depth of its old parent
-- - child.depth is the original depth of the node
-- we're looking at before a parent node moved.
-- note that this is not necessarily old.depth + 1,
-- as we are looking at all depths below the modified
-- node
-- the depth of the node relative to the old parent is
-- (child.depth - old.depth), then we simply add this to the
-- depth of the new parent, plus one.
SET depth = child.depth - old.depth + ISNULL(parent.depth + 1,0),
lineage = ISNULL(parent.lineage,'/') + LTrim(Str(old.id)) + '/' +
right(child.lineage, len(child.lineage) - len(old.lineage))
-- if the parentId has been changed for some row
-- in the "inserted" table, we need to update the
-- fields in all children of that node, and the
-- node itself
FROM dfTree child
INNER JOIN inserted old ON child.lineage LIKE old.lineage + '%'
-- as with the insert trigger, attempt to find the parent
-- of the updated row
LEFT OUTER JOIN dfTree parent ON old.parentId=parent.id
My SQLite version until now:
CREATE TRIGGER IF NOT EXISTS sc_compare_UpdateTrigger
AFTER UPDATE ON dfTree
FOR EACH ROW
BEGIN
UPDATE child
SET depth = child.depth - OLD.depth + IFNULL(parent.depth + 1,0),
lineage = IFNULL(parent.lineage,'/') + LTrim(CAST(OLD.id AS TEXT)) + '/' +
right(child.lineage, len(child.lineage) - len(OLD.lineage))
FROM dfTree child
INNER JOIN inserted OLD ON child.lineage LIKE OLD.lineage + '%'
LEFT OUTER JOIN dfTree parent ON OLD.parentId=parent.id
END;
For this version, an "Error: near "FROM": syntax error" occours.
I'm on the right track? Is it possible to build up this trigger functionality with SQLite?
