Hey, anyone know if it is possible to fire a trigger before a select?
I'm considering creating some tables which contain data summarized from
other tables as kind of a cache mechanism. The hope is I can speed up
some queries that get executed a lot (and are kind of slow) by
maintaining data (like sums of big columns, for example). I was all
ready to go and then I discovered that trigger-before-select is not
supported. (Is it in any DB?)
The idea is this:
Any time I execute a query that would access the summary data, the
"before select" trigger fires and goes out and builds any summary data
missing from the summary table. When I do an insert,update,delete on
the primary data table, another trigger fires that removes the
applicable data from the summary table. This way, I only cache the
information I need in the summary table, right before I need it. But it
can stay there as long as the base information doesn't change so I don't
have to redo the expensive operation of summarizing it any more often
than necessary. Its kind of like an index in a way, but it is not
maintained at insert/update time. Rather, it is updated as it is needed.
Anyone have any ideas about how I can accomplish this?
Kyle