Re: Trigger on select?

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

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

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