Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have written a warehouse management web app. The application handles batch picking, warehouse routing, packing, and the final piece is handled by UPS worldship to "ship" the packages. Worldship will write a record to my postgres db after every shipment or void. I need a way to cleanly see that the record was written/deleted and then "do stuff".

The easy answer is to have a program just monitor the database and when it sees a record written to do its thing but something is nagging at me that there is probably a better way to go about this that doesn't have a program polling the table and comparing it to what was there last.

EDIT: How i was going to approach this... The table is setup with a status field. My program would on intervals read the table for any records that have a null status, if records are found "do stuff" then mark the status as complete.

TIA

share|improve this question
7  
Can't you use triggers? You could CREATE TRIGGER ON every insert/delete and mark a flag in the original data or in a separate table for your program to note when something is changed. Comparing to past data will get really slow really fast. Triggers can have issues on their own (if written poorly) but this is what they're made for. –  Ben Brocka Oct 26 '11 at 13:57
1  
@BenBrocka, +1. This should be the answer. If the logic in the trigger is not too complicated, like inserting a record in another table, performance should not be a big issue. –  Jalayn Oct 26 '11 at 14:25
    
I was going to write triggers myself as an answer, but really @Ben Brocka you should put your comment in as the answer. I will add that the OP should test the trigger using a large multi-record change just to be sure it can handle that as well as one record at a time. –  HLGEM Oct 26 '11 at 14:27
    
@HLGEM Answer posted –  Ben Brocka Oct 26 '11 at 15:45
add comment

2 Answers

up vote 6 down vote accepted

There's at least two other effective means to handle this type of problem.

A. Use triggers. Set them to fire on the appropriate operations.

B. Expose a service. The client program goes through the service instead of directly hooking into the database. This gives you full control over which tables can be updated. The extra layer gives you the ability to handle operations in an imperative language as well. In other words, C#/Java is usually a better choice for item manipulation such as iterating individual rows as opposed to SQL which is best suited for set operations.

share|improve this answer
add comment

Database triggers will allow you to do this most efficiently. Create a trigger on the insert/delete operations in the relevant table so you capture the event you're talking about. Use the trigger to insert a row into a table just for your application to mark when a record has been changed.

Triggers were designed to do this and are very fast, but be careful when writing them; if the trigger's statement fails (e.g. you violate a UNIQUE constraint) the whole operation can fail, not just the triggered action.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.