Subject Re: [firebird-support] it's possible to have a view cache?
Author André Knappstein, Controlling
I did not understand, why triggers are no option in your case.
Whatever service is adding the record also will fire the insert
trigger within the same transaction. No additional logic on the client
side is needed.

If you do not want to use triggers, and if you have privileges to
change the "service" that is adding the records, one choice would be
to redirect the inserting operation to a stored procedure.

This SP will then first try to do the insert and in a next step update
an aggregate table (which you should create beforehand).

This only avoids the constant re-use of aggregating functions, but it
will not save you from constant re-querying (maybe timer-driven).

Remember to restrict inserting privilege to this SP only, and
also to create similar SPs for deleting and updating or your
aggregate table will become inconsistent.

There also theoretically is the option to use Firebird Events (your
client application will register for being notified on
updates/inserts/deletes), but I don't know how difficult this will be
for you to implement.


~~~Ihre Nachricht~~~

k> hi all, i'm newbie to databases so sorry if this is a stupid question :)

k> i have a table where the records are read-only and where new rows
k> are added by a service with a single connection/transaction (so
k> using a trigger on update it's not very good for my purpose).

k> then i have a view (but maybe there's a better solution for my needs)
k> which contains the results of same calculations made on the values
k> of my table (at the moment i don't know if i can do that calcolous
k> with sql, for example with a store procedure, or if i need to use an UDF).

k> i don't know very well how a view works but it seems that is not
k> cached, so every time that i do a select on the my view all the
k> data is reed again from the original table, so for example if i
k> have a column in the view that contains count(*) it will be revaluated on every select.

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

k> what do you suggest?

k> ------------------------------------

k> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

k> Visit and click the Resources item
k> on the main (top) menu. Try Knowledgebase and FAQ links !

k> Also search the knowledgebases at

k> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
k> Yahoo! Groups Links