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 <
[hidden email]> 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 <
[hidden email]>
> 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
> >
[hidden email]
> >
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
>
[hidden email]
>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Locked
