I have a table that is managed via triggers: CREATE TABLE cache ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name VARCHAR(50), value INTEGER );
The idea is to cache values generated from a bunch of other tables for easy access, since it takes quite a long time to select and merge all the data to generate the values, and I access it far more frequently than I update it. When the other tables are updated, the trigger inserts a name/value pair into the cache table. I then have another trigger which fires when I insert into the cache table which deletes any earlier entries for the same name.
This seems a bit over-complicated, but I can't see any other way to do it in the absence of an UPDATE ... INSERT ON DUPLICATE KEY statement like MySQL. Can anyone think of better solution than this?
-- John English