Caching using triggers

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Caching using triggers

John English-3
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