Roman -- You can certainly use a trigger to track changes (as well as creating an audit trail and enforcing rules); performance on inserts,updates and deletes will suffer accordingly since there's extra operations involved, but it definitely be a winner on 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) for examples 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 which procedures don't return values but functions always do. (Someone more knowledgable please correct me if I am wrong on this!). HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ------------------------------------------------------- Click link below if it is SPAM [EMAIL PROTECTED] "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=450038a9268108992556831&[EMAIL PROTECTED]&retrain=spam&template=history&history_page=1" !DSPAM:450038a9268108992556831! ------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
