Hi, I have a hierarchical table -
sqlite> .sch hierarchy CREATE TABLE hierarchy(id integer primary key, parent references hierarchy, descrip text, status text); with some entries - sqlite> select * from hierarchy; id parent descrip status 1 root open 2 1 branch1 open 3 1 branch2 open 4 3 branch22 open 5 4 branch222 open 6 1 branch3 open I can clear the status field of the second branch (i.e id = 3, 4 and 5) with - with recursive cte(lev, id, parent, status) as (select 0, id, parent, status from hierarchy where id=3 union all select lev+1, h.id, h.parent, h.status from hierarchy as h, cte where h.parent=cte.id) update hierarchy set status=null where id in (select id from cte); sqlite> select * from hierarchy; id parent descrip status 1 root open 2 1 branch1 open 3 1 branch2 4 3 branch22 5 4 branch222 6 1 branch3 open However, if I try to create a trigger with this statement - sqlite> create view vhierarchy as select * from hierarchy; sqlite> create trigger thierarchy instead of delete on vhierarchy begin with recursive cte(lev, id, parent, status) as (select 0, id, parent, status from hierarchy where id=old.id union all select lev+1, h.id, h.parent, h.status from hierarchy as h, cte where h.parent=cte.id) update hierarchy set status=null where id in (select id from cte); end; I get an error - Error: near "update": syntax error Can anyone see why? _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
