Can a trigger recursively update a table?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Can a trigger recursively update a table?

Shane Dev
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

J. King-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

Igor Tandetnik-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

Shane Dev
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

Clemens Ladisch
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

Shane Dev
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

Clemens Ladisch
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
Reply | Threaded
Open this post in threaded view
|

Re: Can a trigger recursively update a table?

Shane Dev
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