Isolation / Visibility inside a trigger

Hi!


I'm trying to fix a bug (?) in my design but I'd like to understand my mistake
first, so that I don't do that again.

I'm inserting rows into a table that are results from an external physical
process and I have some operations that I was automating inside the database
with triggers:

     - setting the data input timestamp (more than one table used here)

     - doing some cross calculations with results from other processes already
       entered on the database and updating a specific row
       (e.g. NEW.calculated_result)

     - checking the final result ('calculated_row' as above) and comparing it
       against a range of values to let it go or not as an automated result to
       the client (hence setting something like NEW.let_result_go to either
       TRUE or FALSE)

     - if the result was authorized by the technician to be freed to customers
       then marking it as manually authorized and setting the above column
       (NEW.let_result_go = TRUE) plus some other auditing columns
       (NEW.authorized_by, NEW.authorized_at).


The problem is that some of these rules are somewhat complex and since I
needed to provide some feedback first on the interface I had them developed
receiving the ID of the material, the value read from the equipment and then
did the calculations (second item from the above list) so that I could show
the result on the application interface.

But when I converted those to (before) triggers I started having a problem
where it tries reading data from the soon-to-be-commited row but the functions
called can't read it, even though the serial column has already been
incremented and the insert command issued.

I tried passing the NEW row as a parameter but I get the same error when
recovering data from it, where it says that this data doesn't exist in the
database.

So, I have two (big) doubts here (among other small ones that I have to think
more):

    - shouldn't the data be available inside the transaction and visible for
      all operations called by the trigger?

    - shouldn't I use before triggers when manipulating data and changing
      values (since after triggers ignore results)?


What would be a good design to have this done?  In a "perfect world" all
should be done at the same time.


I'm using this to start my transactions:

    BEGIN;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


Any hints would be greatly appreciated.


Thanks in advance,
--
Jorge Godoy      <jgodoy@gmail.com>