Re: [sqlite] Is foreign key support disabled within triggers with raise functions?

[David Raymond]

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 <devshan...@gmail.com> 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?

[Richard Hipp]

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?

[Shane Dev]

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?

[Richard Hipp]

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?

[Shane Dev]

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?

[Richard Hipp]

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