Thanks for the wonderfully simple and concise solution. I see now triggers do support CTEs if they SELECT a RAISE() function. I never thought of using a BEFORE trigger.
Fijne kerstdagen On 24 December 2017 at 17:17, E.Pasma <pasm...@concepts.nl> wrote: > On 24/12/2017 11:56, Shane Dev wrote: > > Related to my previous question >> https://www.mail-archive.com/sqlite-users@mailinglists.sqlit >> e.org/msg107527.html, >> I want to prevent the client from inserting a cycle. >> >> For example - >> >> sqlite> .sch edges >> CREATE TABLE edges(parent integer not null, child integer not null, >> constraint self_reference check (parent<>child)); >> >> sqlite> select * from edges; >> parent child >> 1 2 >> 1 3 >> 2 4 >> >> insert into edges select 2, 5; -- ok >> insert into edges select 2, 1; -- should not be allowed. >> insert into edges select 4, 1; -- should not be allowed. >> >> Many kinds of insertions can be prevented using triggers. Existing cycles >> can be detected using a recurisve common table expression. However, since >> CTEs are not supported inside triggers, I assume they can't be used for >> this purpose. Is there another way? >> >> > > Sorry for ignoring the two earlier repiies, but it looks that WITH can be > used inside triggers. Like > > create trigger ins_edges before insert on edges > begin > with recursive r as ( > select new.child > union all > select edges.child > from r > join edges on edges.parent=r.child > ) > select raise (FAIL, 'example error') > from r where child=new.parent; > end > ; > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
