Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
I think the thing here is... "...But the FAIL resolution does not back out prior changes of the SQL statement that failed ..." Emphasis on the last part. So you would think that in autocommit mode you would wind up in the middle of your "implicitly created" transaction, which you could then roll back. The problem appears to be that it winds up committing the data from the implicit transaction. sqlite> insert into vtrig select 5; Error: this statement seems to to temporarily disable foreign support sqlite> select * from deptab; id|ref 1|2 sqlite> rollback; Error: cannot rollback - no transaction is active -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, January 12, 2018 8:37 AM To: SQLite mailing list Subject: Re: [sqlite] Is foreign key support disabled within triggers with raise functions? On 1/12/18, Shane Dev wrote: > Does that mean FK constraints are only checked > if processing reaches the end of the trigger? FKs are checked at the end of the entire statement. If multiple triggers fire, then they all run to completion before any FKs are checked. -- 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
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
On 1/12/18, Shane Dev wrote: > Does that mean FK constraints are only checked > if processing reaches the end of the trigger? FKs are checked at the end of the entire statement. If multiple triggers fire, then they all run to completion before any FKs are checked. -- 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
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
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 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 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 wrote: > > > >> On 1/11/18, Shane Dev 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
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
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 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 wrote: > >> On 1/11/18, Shane Dev 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
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
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 wrote: > On 1/11/18, Shane Dev 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
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
On 1/11/18, Shane Dev 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] Is foreign key support disabled within triggers with raise functions?
Hello, Table deptab has a foreign key relationship with table reftab - sqlite> .sch CREATE TABLE reftab(id integer primary key); CREATE TABLE deptab(id integer primary key, ref int references reftab); foreign key support is enabled - sqlite> pragma foreign_keys; foreign_keys 1 the referenced table is empty - sqlite> select * from reftab; sqlite> so the following insert fails and deptab remains empty as expected - sqlite> insert into deptab(ref) select 1; Error: FOREIGN KEY constraint failed sqlite> select * from deptab; sqlite> Now I define the following view and trigger - 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? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users