Hello,

Perhaps it would be clearer if I ask the question in a different way. Why
does the following statement -

insert into vtrig select 5;

fail to insert a record in Test 1 below (as expected) but succeeds in Test
2 (despite the foreign key constraint)?

sqlite> CREATE TABLE reftab(id integer primary key);
sqlite> CREATE TABLE deptab(id integer primary key, ref int references
reftab);
sqlite> pragma foreign_keys;
foreign_keys
1
sqlite> CREATE VIEW vtrig as select 1;

Test 1
=====

sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
   ...> insert into deptab(ref) select 2;
   ...> end;
sqlite> insert into vtrig select 5;
Error: FOREIGN KEY constraint failed
sqlite> select * from deptab;
sqlite>


Test 2
=====

sqlite> drop trigger ttrig;
sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin
   ...> insert into deptab(ref) select 2;
   ...> select raise(FAIL, 'this statement seems to temporarily disable
foreign
   ...> support');
   ...> end;
sqlite> insert into vtrig select 5;
Error: this statement seems to temporarily disable foreign
support
sqlite> select * from deptab;
id      ref
1       2


On 12 January 2018 at 00:48, Richard Hipp <d...@sqlite.org> wrote:

> On 1/11/18, Shane Dev <devshan...@gmail.com> wrote:
> >
> > CREATE VIEW vtrig as select 1;
> > CREATE TRIGGER ttrig instead of insert on vtrig begin
> >   delete from deptab;
> >   delete from reftab;
> >   insert into deptab(ref) select 2;
> >   select raise(FAIL, 'this statement seems to temporarily disable foreign
> > support') where (select count(*) > 0 from deptab);
> > end;
> >
> > sqlite> insert into vtrig select 5;
> > Error: this statement seems to temporarily disable foreign support
> > sqlite> select * from reftab;
> > sqlite> select * from deptab;
> > id      ref
> > 1       2
> > sqlite> pragma foreign_keys;
> > foreign_keys
> > 1
> >
> > Can we conclude foreign key support is disabled within triggers with
> raise
> > functions?
>
> I don't what you mean by "disabled".  When you hit a raise(FAIL)
> SQLite stops whatever it was doing and leave the database in whatever
> half-way completed state it was in at the moment.  That's what
> raise(FAIL) is suppose to do.
>
> Did you mean to do "raise(ABORT)" instead, which should do what I
> think you are trying to accomplish.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> 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

Reply via email to