At 12:51 AM 12/01/2010, you wrote:

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

Stupid questions are those that come with insufficient information for anyone to answer them. So this is not a stupid question. ;-)

>i have a table where the records are read-only

Are we talking about a Firebird database? Firebird does not have "read-only" tables. You can make a *database" read-only - but then nothing can be added, changed or deleted.

Assuming Yes (it is a Firebird database) --

> 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).

No; but using a trigger on INSERT is what you need when new rows are added.

>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

If you have a view that contains the results of calculations made on the values in the underlying tables, then you are already doing the calculations with SQL. A view is nothing but a pre-compiled SQL SELECT statement. Whatever you are doing now with that view, you can do (often more quickly) with a dynamic SQL SELECT statement.

>, for example with a store procedure, or if i need to use an UDF).

Firebird stored procedures come in two broad types, which we refer to as "executable" and "selectable", respectively.

-- With the executable type, you can perform all manner of operations on data (including INSERT operations where you want to manipulate data before storing it, and/or generate operations on other tables).

-- With the selectable type, you have the opportunity to read and manipulate data from tables and generate a result set of "virtual data" to return to the client.

From the description you have provided, it seems unlikely that a selectable SP would be useful for your stated purpose, since apparently humans are not viewing any data.

UDFs do not (or SHOULD NOT) perform operations on tables at all. They are external functions, that take 0 to many input arguments. They are used in an SQL expression, to return a single value as a result. Their use is to manipulate data at the field level - very much akin to functions in programming languages.

Depending on what your calculation requirements are, a UDF could be a very useful way to manipulate your incoming data. Also, if you are using a recent version of Firebird (v.2.1 or higher), do not overlook the internal functions, which are used in the same way.

>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.

It is true that the output for every SQL statement is regenerated every time it is requested. Indeed, that is how relational databases work. When a SELECT statement request is executed, the engine fills up a prepared buffer (or "cache") on the server side with the requested output rows. These rows wait there, for the calling application to request them, one by one. Note that this buffer is available only to the transaction from which that statement was requested.

>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.

It seems that you need to consider why a view is needed at all and why an automated client application needs access to virtual data (the results of calculations). Those virtual values can be accommodated by expressions that operate directly on the raw data, either by a dynamic INSERT statement to the destination table, that uses the expressions to manipulate the input data itself; or, if it is too complex to be performed by dynamic SQL + BEFORE INSERT triggers, then by calling a stored procedure (EXECUTE PROCEDURE) that does the tricky work.

>what do you suggest?

I would always suggest making a diagram with simple pencil and paper that depicts exactly what the client application wants to do and how the server can achieve it for you. From your description, I suspect your drawing will reveal that you do not need to operate on an output set at all.

I think you will end up with one of two possible solutions or, perhaps, a combination of both:

1. A parameterised DSQL INSERT statement that will trigger off the processing of the input parameters and store the resulting manipulated data in your table[s];

and/or

2. A parameterised EXECUTE PROCEDURE statement that passes the raw input to an executable stored procedure, where calculations that cannot be done in the BEFORE INSERT triggers are performed and the INSERT statement[s] are eventually called.

./heLen