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?