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
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!
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
INSERT? Is there any more concise way?