Sunday, 1 September 2013

Replace DELETE for UPDATE with a trigger on SQLite

Replace DELETE for UPDATE with a trigger on SQLite

Is it possible in SQLite to make an update instead of a delete within a
trigger ? I.e, I got these two tables:
CREATE TABLE author (authorid INTEGER PRIMARY KEY, temporal NUMERIC);
CREATE TABLE comment (id INTEGER PRIMARY KEY, text TEXT, authorid INTEGER,
FOREIGN KEY(authorid) REFERENCES author(authorid));
When a deletion of an author is attempted and there's any comment
referencing that author i want to update the "temporal" field and abort
deletion.
I've tested different approaches with triggers but i have not found a way
to do the two things, make the update and abort the delete. I can abort
the delete (though in this case it's not necessary as it is enforced by
the foreign key constraint) or make the update (though the delete will
remove the record, so the update has no effect)

No comments:

Post a Comment