Re: [sqlite] Can a trigger recursively update a table?

brilliant! - it works - thanks On 14 December 2017 at 19:07, Clemens Ladisch wrote: > Shane Dev wrote: > > On 14 December 2017 at 12:59, Clemens Ladisch > wrote: > >> Shane Dev wrote: > >>> Can we conclude there is no single CTE or other SQL statement

Re: [sqlite] Can a trigger recursively update a table?

Shane Dev wrote: > On 14 December 2017 at 12:59, Clemens Ladisch wrote: >> Shane Dev wrote: >>> 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? >> >> That should be possible when

Re: [sqlite] Can a trigger recursively update a table?

Hi Clemens, With your solution, how would you define the DELETE ON VHIERARCHY trigger? On 14 December 2017 at 12:59, Clemens Ladisch wrote: > Shane Dev wrote: > > Can we conclude there is no single CTE or other SQL statement which can > > update a branch of the tree

Re: [sqlite] Can a trigger recursively update a table?

Shane Dev wrote: > 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? That should be possible when you enable recursive triggers: begin update hierarchy set status = null where id = old.id;

Re: [sqlite] Can a trigger recursively update a table?

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

Re: [sqlite] Can a trigger recursively update a table?

On 12/12/2017 6:44 PM, Shane Dev wrote: However, if I try to create a trigger with this statement - http://www.sqlite.org/lang_with.html """ Limitations And Caveats - The WITH clause cannot be used within a CREATE TRIGGER. """ -- Igor Tandetnik ___

Re: [sqlite] Can a trigger recursively update a table?

CTEs cannot be used inside triggers for UPDATE statements. See near the bottom of: On December 12, 2017 6:44:35 PM EST, Shane Dev wrote: >Hi, > >I have a hierarchical table - > >sqlite> .sch hierarchy >CREATE TABLE hierarchy(id

[sqlite] Can a trigger recursively update a table?

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 rootopen 2 1