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
