Search Postgresql Archives

Re: Database design and triggers...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:	pgsql-general-owner@xxxxxxxxxxxxxx on behalf of romantercero@xxxxxxxxx
Sent:	Wed 9/6/2006 11:05 AM
To:	pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly


-------------------------------------------------------
Click link below if it is SPAM gsw@xxxxxxxxxxxxxxxx
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=450038a9268108992556831&user=gsw@xxxxxxxxxxxxxxxx&retrain=spam&template=history&history_page=1";
!DSPAM:450038a9268108992556831!
-------------------------------------------------------







[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux