0

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?

metamagikum
  • 1,307
  • 15
  • 19

1 Answers1

0

Sqlite UPDATE syntax do not support FROM clause. Note also there's no insetred table, only NEW, OLD rows in FOR EACH ROW trigger.

Serg
  • 22,285
  • 5
  • 21
  • 48