CODEWITHSUNDEEP
c++sqlqtsqlite
borisbn
borisbn

Reputation: 5054

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

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.

Upvotes: 1

Views: 1755

Answers (2)

AndruWitta
AndruWitta

Reputation: 196

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

SQLite Documentation

Upvotes: 0

BartoszKP
BartoszKP

Reputation: 35891

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.

Upvotes: 1

Related Questions