How to check whether row was inserted or replaced in SQLite?

Question

I have a SQLite database and I created an unique index like this:

CREATE UNIQUE INDEX [my_unique_idx] ON [my_table] ([field1], [field2]);

Now in my program I want to INSERT OR REPLACE a row in my_table and if it was inserted I need to insert other rows in another "slave" table. On the other hand, if the row was updated I need to do nothing. In other words:

if ( query.exec(
    "insert or replace into my_unique_idx"
    "    (field1, field2, other_field)"
    "  values"
    "    (1, 2, 'foo')"
   ) )
{
    if ( query.is_was_really_inserted() ) // <---- how to ?
    {
        slave_query.exec( "insert into slave_table......
    }
}

I know, that I can do it with several queries: select a row with appropriate values and if row doesn't exists then insert data into both tables ("master" and "slave"), and just update "master" table if row exists.
But this makes the unique index and INSERT OR REPLACE clause senseless.

The index speeds up searching for the record. INSERT OR REPLACE is useful only if you don't care what actually happended. — CL., Sep 10 '15 at 16:44
@CL. Thank you. Of course I use indexes, but not compaund - one for own field. BTW, what is quickly: using one index for one particular field or one index for all fields, that would be in `where` in further `select`s ? — borisbn, Sep 11 '15 at 07:06
What index is more useful depends on the query and the data. If you don't know better, try both indexes (run [ANALYZE](http://www.sqlite.org/lang_analyze.html) and check with [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html).) — CL., Sep 11 '15 at 07:16

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

This seems to be possible with triggers. SQLite documentation states:

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

[...]

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

Thus, if you had a delete trigger you will know whether any row is being deleted in order to replace it.

You can either:

Use that trigger to update some intermediate table, and then after your query run another query to check which rows have been updated (seems to be the worst solution).
Use that trigger to ensure your database consistency (not bad, however you will have some logic fragmentation between code and scripts).
use Qt to register for a DB event that the trigger will invoke: see this answer. Seems that SQLite supports event notifications.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 10 '15 at 14:04

BartoszKP

34,786
15
102
130

Ok, thank you. But how could I know that delete trigger was fired in my program (not inside QSLite) ? Note, that I need to know it to insert or not other data in other table. – borisbn Sep 10 '15 at 15:07
Seems that SQLite suppurts [event notifications](https://www.sqlite.org/c3ref/update_hook.html). I'll try this... Or... I'll remove `OR REPLACE` and use several queries in Qt's program. Maybe it would the easiest decision – borisbn Sep 10 '15 at 15:19
@borisbn Thanks for the tip, I'll put it in the post for future reference. Good luck! – BartoszKP Sep 10 '15 at 15:22
2

Note that the SQLite callbacks work only on changes done from within one instance of sqlite library. If you have multiple processes or library instances accessing the same database, the notifications will be limited to changes made by each instance only. – Kuba hasn't forgotten Monica Sep 10 '15 at 21:24
@KubaOber Thank you. Never knew about it – borisbn Sep 11 '15 at 07:02
@borisbn Sqlite is not a server. It has no way of detecting what someone else did to a database while it wasn't looking (as in: its code wasn't executing in your process). – Kuba hasn't forgotten Monica Sep 11 '15 at 12:46

score 0 · Answer 2 · answered Oct 22 '20 at 02:24

0

The SQLiteConnection property LastInsertRowId seems to offer a simpler solution, depending on the way the indexes are working in your table.

SQLite Documentation

answered Oct 22 '20 at 02:24

AndruWitta

186
3
6

I have tested this solution - it does not work for me. INSERT or REPLACE in case of REPLACE deletes row first and then inserts new one. So LastInsertRowId is incremented in any case – user3431635 Mar 06 '21 at 15:18

How to check whether row was inserted or replaced in SQLite?

2 Answers2