Thread: Trigger on select?

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

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

On Tue, Aug 02, 2005 at 05:54:59PM -0700, Chris Travers 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.

And then it needs to be a materialized view of some sort
to achieve the caching he's after.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346