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

Reply via email to