Thanks for your answers, I missed that part of the create trigger documentation.
Can we conclude there is no single CTE or other SQL statement which can update a branch of the tree starting with a flexibly specified node? i.e. I have to "hard-code" the starting node (top of branch) in my CTE like this - with recursive cte(lev, id, parent, status) as (select 0, id, parent, status from hierarchy -- change the start node in the line below 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); On 13 December 2017 at 00:59, J. King <[email protected]> wrote: > CTEs cannot be used inside triggers for UPDATE statements. See near the > bottom of: > <http://sqlite.org/lang_createtrigger.html> > > On December 12, 2017 6:44:35 PM EST, Shane Dev <[email protected]> > wrote: > >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 > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
