INSERT OR REPLACE statement and triggers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

INSERT OR REPLACE statement and triggers

skywind mailing lists
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... .)

Regards,
Hartwig

_______________________________________________
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: INSERT OR REPLACE statement and triggers

R Smith-2


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

Re: INSERT OR REPLACE statement and triggers

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

Re: INSERT OR REPLACE statement and triggers

skywind mailing lists
Hello,

thanks for the documentation references!

Regards,
Hartwig

> Am 2017-12-23 um 13:18 schrieb J. King <[hidden email]>:
>
> 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 <[hidden email]> 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
>> [hidden email]
>> 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
> [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: INSERT OR REPLACE statement and triggers

David Raymond
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:[hidden email]] 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 <[hidden email]>:
>
> 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 <[hidden email]> 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
>> [hidden email]
>> 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
> [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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users