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
