Construct a trigger that deletes existing rows before inserting new ones in SQLite, which lacks FOR EACH STATEMENT

deleteinsertsqlitetrigger

Here’s my SQLite schema:

CREATE TABLE IF NOT EXISTS deps (
    target TEXT NOT NULL,
    user TEXT NOT NULL,
    dependency TEXT NOT NULL);

CREATE INDEX IF NOT EXISTS targetUser ON deps (target, user);

In words, the deps table (a dependency tree) is intended to have multiple
rows for each (target, user) pair—all rows with the same (target, user) pair are dependencies of target for some user.

In my application, a user only batch-updates the dependencies of a target. By “batch” I mean that I would (1) DELETE all rows containing the (target, user) and then (2) INSERT the new rows fresh.

I would like a trigger to combine these two steps in the SQLite database, so that before I insert new rows for a given (target, user), the database deletes all old rows for this pair. I came up with this:

CREATE TRIGGER IF NOT EXISTS clearOldDeps BEFORE INSERT ON deps
    BEGIN
        DELETE FROM deps WHERE target = NEW.target AND user = NEW.user;
    END

but alas! SQLite triggers are all FOR EACH ROW, and it lacks FOR EACH STATEMENT, so if I use this trigger above and insert some rows for a given (target, user) pair, each row inserted deletes the rows that were inserted just before!

Observe:

sqlite> INSERT INTO deps VALUES ("t1", "u2", "e1"), ("t1", "u2", "e2"),("t1", "u2", "e3");
sqlite> select * from deps;
t1|u2|e3

I went to add three rows, and wanted any pre-existing rows for ("t1", "u2") target–user pair to be deleted, but I only got the last row inserted, because it deleted all previous ones.

If SQLite had FOR EACH STATEMENT triggers, I suspect this trigger could be made to work using that, but since it doesn’t, is my only recourse to first DELETE then INSERT? Is there any more concise way?

Best Answer

Remember that this is a lite version of SQL and does not support all operations. See the following link:

https://www.sqlite.org/lang_createtrigger.html

SQLite supports a BEFORE TRIGGER which can delete rows before further action is taken. The link also provides some cautions about how that works:

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

See the link to learn other details about the SQLite triggers.

Of course you could write your SQLite code to delete the rows that you no longer need in your database before inserting new rows into your database. I would prefer that approach over a trigger since your are just getting rid of unwanted data.

Related Question