Hi,

> hi all, i'm newbie to databases so sorry if this is a stupid question :)
>
> i have a table where the records are read-only and where new rows are
> added by a service with a single connection/transaction (so using a
> trigger on update it's not very good for my purpose).
>
> then i have a view (but maybe there's a better solution for my needs)
> which contains the results of same calculations made on the values of my
> table (at the moment i don't know if i can do that calcolous with sql, for
> example with a store procedure, or if i need to use an UDF).
>
> i don't know very well how a view works but it seems that is not cached,
> so every time that i do a select on the my view all the data is reed again
> from the original table, so for example if i have a column in the view
> that contains count(*) it will be revaluated on every select.
>

That is correct, this is what a view does.

> my idea is to have samething as a cache of the view wich will be reloaded
> only when the original table is updated (i mean when the transaction that
> had updated the table has been commited, not on the insertion of every
> single new row), otherwise it will contains the static results of the
> calcolous based on the values of the table.
>
> what do you suggest?

Firebird does not support this automatically, it seems that what
you want is something known as "materialized views".

From what I understand, you should use a physical database table
and insert rows yourself from the application that does the inserting
in the other tables.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com