Thread: the best way to catch table modification

Hello,
I'm implementing db-queries caching system - for this I need to know
each table's modification time (or at least modification counter). I
know that I can make a statement-level trigger, which will update a
table with tables modification times - however this is inefficient if
many inserts or updates are made on single table (in single
transaction). The best would be some kind of transaction-level trigger,
but this is not available. Are there any other, better options ?

Thanks in advance.
ML

Hi,

  I posted on the same subject a month ago . .you can search for the
current title in the JDBC mailing list
[JDBC] implementing asynchronous notifications PLEASE CONFIRM MY

I ended using statement-level trigger.  I haven't found another way to
do it .

Regards
/David

Marek Lewczuk wrote:

> Hello,
> I'm implementing db-queries caching system - for this I need to know
> each table's modification time (or at least modification counter). I
> know that I can make a statement-level trigger, which will update a
> table with tables modification times - however this is inefficient if
> many inserts or updates are made on single table (in single
> transaction). The best would be some kind of transaction-level
> trigger, but this is not available. Are there any other, better options ?
>
> Thanks in advance.
> ML
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

Am Dienstag, 25. Oktober 2005 19:40 schrieb David Gagnon:
> Hi,
>
>   I posted on the same subject a month ago . .you can search for the
> current title in the JDBC mailing list
> [JDBC] implementing asynchronous notifications PLEASE CONFIRM MY
>
> I ended using statement-level trigger.  I haven't found another way to
> do it .
>
> Regards
> /David
>
> Marek Lewczuk wrote:
> > Hello,
> > I'm implementing db-queries caching system - for this I need to know
> > each table's modification time (or at least modification counter). I
> > know that I can make a statement-level trigger, which will update a
> > table with tables modification times - however this is inefficient if
> > many inserts or updates are made on single table (in single
> > transaction). The best would be some kind of transaction-level
> > trigger, but this is not available. Are there any other, better options ?

What did you mean with "many inserts or updates"? Did you mean statements
which modify or insert many rows but are still one single statement: you
could use rules instead. I think rules are much more powerful than triggers
and they are much faster if a statement affects many rows. Triggers fires for
each row, rules are just modifying the original statement. so usually if you
can handle the load of the statements you should handle the loads of
statements rewritten by rules, too. Rules are usually much more efficient
than triggers. And they are much more relational in my opinion.

kind regards,
janning