Is foreign key support disabled within triggers with raise functions?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Is foreign key support disabled within triggers with raise functions?

Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 1/11/18, Shane Dev <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 1/11/18, Shane Dev <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
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 <[hidden email]> 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 <[hidden email]> wrote:
>
>> On 1/11/18, Shane Dev <[hidden email]> 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
>> [hidden email]
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> wrote:
> >
> >> On 1/11/18, Shane Dev <[hidden email]> 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
> >> [hidden email]
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 1/12/18, Shane Dev <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <nor does it end the transaction>..." 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:[hidden email]] 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 <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users