Thanks for the explanation. Does that mean FK constraints are only checked if processing reaches the end of the trigger?
On 12 January 2018 at 13:29, Richard Hipp <d...@sqlite.org> wrote: > Test 1 makes the changes, the checks the FK constraints, sees that the > FK constraints are violated and hence runs ABORT, which backs out the > change. > > Test 2 makes the change, then runs FAIL, which stops all further > processing. The FK constraints are never checked, and the changes are > not backed out. > > On 1/12/18, Shane Dev <devshan...@gmail.com> wrote: > > 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 > > > > > -- > 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
