Result of a test with recursive off then on.
"before insert" fired on "insert or ignore"
With recursive triggers on, the before and after delete triggers fired between
the before and after insert triggers.
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (id integer primary key, foo text);
sqlite> create table trigActions (trig text, oldID int, newID int, oldFoo text,
newFoo text);
sqlite> create trigger bi before insert on t begin insert into trigActions
values ('Before Insert', null, new.id, null, new.foo); end;
sqlite> create trigger ai after insert on t begin insert into trigActions
values ('After Insert', null, new.id, null, new.foo); end;
sqlite> create trigger bu before update on t begin insert into trigActions
values ('Before Update', old.id, new.id, old.foo, new.foo); end;
sqlite> create trigger au after update on t begin insert into trigActions
values ('After Update', old.id, new.id, old.foo, new.foo); end;
sqlite> create trigger bd before delete on t begin insert into trigActions
values ('Before Delete', old.id, null, old.foo, null); end;
sqlite> create trigger ad after delete on t begin insert into trigActions
values ('After Delete', old.id, null, old.foo, null); end;
sqlite> insert into t values (1, 'one');
sqlite> insert into t values (2, 'two');
sqlite> insert into t values (3, 'three');
sqlite> insert into t values (1, 'new one');
Error: UNIQUE constraint failed: t.id
sqlite> insert or replace into t values (2, 'new two');
sqlite> insert or ignore into t values (3, 'new three');
sqlite> select * from trigActions order by rowid;
trig|oldID|newID|oldFoo|newFoo
Before Insert||1||one
After Insert||1||one
Before Insert||2||two
After Insert||2||two
Before Insert||3||three
After Insert||3||three
Before Insert||2||new two
After Insert||2||new two
Before Insert||3||new three
sqlite> pragma recursive_triggers;
recursive_triggers
0
sqlite> pragma recursive_triggers = true;
sqlite> delete from t;
sqlite> delete from trigActions;
sqlite> insert into t values (1, 'one');
sqlite> insert into t values (2, 'two');
sqlite> insert into t values (3, 'three');
sqlite> insert into t values (1, 'new one');
Error: UNIQUE constraint failed: t.id
sqlite> insert or replace into t values (2, 'new two');
sqlite> insert or ignore into t values (3, 'new three');
sqlite> select * from trigActions order by rowid;
trig|oldID|newID|oldFoo|newFoo
Before Insert||1||one
After Insert||1||one
Before Insert||2||two
After Insert||2||two
Before Insert||3||three
After Insert||3||three
Before Insert||2||new two
Before Delete|2||two|
After Delete|2||two|
After Insert||2||new two
Before Insert||3||new three
sqlite>
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of skywind mailing lists
Sent: Saturday, December 23, 2017 9:10 AM
To: SQLite mailing list
Subject: Re: [sqlite] INSERT OR REPLACE statement and triggers
Hello,
thanks for the documentation references!
Regards,
Hartwig
> Am 2017-12-23 um 13:18 schrieb J. King <jk...@jkingweb.ca>:
>
> The documentation states that in cases of replacement the DELETE trigger only
> fires if recursive triggers are enabled. Recursive triggers are currently off
> by default.
>
> <https://sqlite.org/lang_conflict.html>
> <https://sqlite.org/pragma.html#pragma_recursive_triggers>
>
> On December 23, 2017 7:05:59 AM EST, R Smith <ryansmit...@gmail.com> wrote:
>>
>>
>> On 2017/12/23 1:57 PM, skywind mailing lists wrote:
>>> Hello,
>>>
>>> I have checked the documentation but did not find an explicit answer
>> concerning my case. Of course I can test it but this does not mean that
>> the functionality is guaranteed also for future versions. Assume I have
>> an insertion statement like
>>>
>>> INSERT OR REPLACE INTO TestTable VALUES(1,2);
>>>
>>> Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the
>> table. Which triggers are triggered in case of
>>>
>>> a) insertion (pretty obvious, should always be the INSERT trigger)
>>> b) replace (DELETE and INSERT trigger or UPDATE trigger or depends
>> on... .)
>>
>> INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then
>> INSERT" based on whether or not the Constraint/Key is violated, so you
>> are guaranteed to have the INSERT trigger fire, but if the item did
>> exist before, the DELETE trigger will fire first.
>>
>> HTH,
>> Ryan
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> _______________________________________________
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
