Kyle Bateman wrote:
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.
No. You must instead generate a view.
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?
something like create view wrapper_table as
select * from original table where (select pseudo_trigger_function())
IS TRUE;
The above example is off the top of my head. It may require some editing.
Best Wishes,
Chris Travers
Metatron Technology Consulting
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
