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@xxxxxxxxx>
