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

Roman --

You can certainly use a trigger to track changes (as well as creating an audit trail and enforcing rules); performance
oninserts,updates and deletes will suffer accordingly since there's extra operations involved, but it definitely be a
winneron the other end in generating reports and quick totals. 

As long as data changes are properly rolled into a transaction I can't think of any obvious ways this setup would fail
--the trigger changes would also be committed or rolled back, but you do need to pay attention to when your trigger
fires(before or after). 

See for instance <http://www.postgresql.org/docs/8.1/interactive/triggers.html> (section 33 of the 8.1.4 documentation)
forexamples and a discussion of the different types. 

And from the point of view of PostgreSQL function and procedure are used interchangably; its not like some languages in
whichprocedures don't return values but functions always do. (Someone more knowledgable please correct me if I am wrong
onthis!). 

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-----Original Message-----
From:    pgsql-general-owner@postgresql.org on behalf of romantercero@gmail.com
Sent:    Wed 9/6/2006 11:05 AM
To:    pgsql-general@postgresql.org
Cc:
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


-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=450038a9268108992556831&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:450038a9268108992556831!
-------------------------------------------------------