db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Caching using triggers
Date Tue, 22 Sep 2020 13:44:56 GMT
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

Mime
View raw message