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.


  • 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.
    – Zelda
    Commented Oct 26, 2011 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
    Commented Oct 26, 2011 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
    Commented Oct 26, 2011 at 14:27
  • @HLGEM Answer posted
    – Zelda
    Commented Oct 26, 2011 at 15:45

2 Answers 2


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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