Can a trigger recursively update a table?
Locked
 
|
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 |
|
|
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 |
Re: Can a trigger recursively update a table?
|
|
In reply to this post by Shane Dev
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 _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
|
|
In reply to this post by J. King-3
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 |
Re: 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; delete from vhierarchy where parent = old.id; end; Regards, Clemens _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Hi Clemens,
With your solution, how would you define the DELETE ON VHIERARCHY trigger? On 14 December 2017 at 12:59, Clemens Ladisch <[hidden email]> 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 you enable recursive triggers: > > begin > update hierarchy set status = null where id = old.id; > delete from vhierarchy where parent = old.id; > end; > > > Regards, > Clemens > _______________________________________________ > 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 |
Re: Can a trigger recursively update a table?
|
|
Shane Dev wrote:
> On 14 December 2017 at 12:59, Clemens Ladisch <[hidden email]> 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 you enable recursive triggers: >> >> begin >> update hierarchy set status = null where id = old.id; >> delete from vhierarchy where parent = old.id; >> end; > > With your solution, how would you define the DELETE ON VHIERARCHY trigger? That is the trigger. Regards, Clemens _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
|
|
brilliant! - it works - thanks
On 14 December 2017 at 19:07, Clemens Ladisch <[hidden email]> wrote: > Shane Dev wrote: > > On 14 December 2017 at 12:59, Clemens Ladisch <[hidden email]> > 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 you enable recursive triggers: > >> > >> begin > >> update hierarchy set status = null where id = old.id; > >> delete from vhierarchy where parent = old.id; > >> end; > > > > With your solution, how would you define the DELETE ON VHIERARCHY > trigger? > > That is the trigger. > > > Regards, > Clemens > _______________________________________________ > 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 |
«
Return to SQLite
|
1 view|%1 views
| Free forum by Nabble | Edit this page |