Hi everybody. Hope I'm posting in the correct group.
My question is part design and part implementation.
Since we are creating an inventory system we need to have the clients
pull up current inventory. Also, we need to have the past transactions
stored for reference and billing. In our previous system in MS Access
we accomplished this by adding up all of the transactions stored in two
tables and generating a temporary table with the latest inventory
count. The problem with this approach is that it is slow because the
temporary table has to be created every time a user needs to see a
report or work on a form. Even when instead of creating a temporary
table we use a query it is still slow. With postgreSQL I found out
about triggers and I figure that instead of calculating the current
inventory count and storing it in a table every time a client needs it
I could have a triggers maintain a table with the current count by
incrementing or decreasing the amounts each time a transaction is
stored in the transaction tables. My worry is that if for some reason a
trigger were to somehow fail to execute correctly there would be an
inconsistency between the transactions table and the current inventory
count table and it would have to be calculated from scratch taking in
to account all of the past transactions in the transactions table.
Are trigger a very safe way to use in the way I describe? Or should I
try using views or stick with the temporary table solution we already
have?
My second part of the question is if there is a tutorial for triggers
and stored procedures and what is the difference between Procedures and
Functions?
Thanks Beforehand!
