Re: Database design and triggers... - Mailing list pgsql-general

It's not clear to me how your data is organized or exactly what you're
counting.  If I understand you correctly, yes, you could use triggers to
maintain a table in this manner.  However, why can't you simply use a
SELECT query using the SUM() or COUNT() aggregate functions?  If the
queries are slow, do some index tuning.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
romantercero@gmail.com
Sent: Wednesday, September 06, 2006 2:05 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Database design and triggers...

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!


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly