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
