The relationship structure between tables below is not the most convenient and technical. It's just to illustrate what I need.
I've read quite a few articles about function, trigger functions and I'm having a problem, because in one part I got an answer: I need to update on two different tables in the same procedure and check if the second table is consistent with the first one.
I explain:
a) I have a table (tabfaturas) that keeps invoices issued and that has a field for 'invoice number', another for the 'financial volume' of the orders that comprise an invoice and another one to indicate the 'status' 'of outstanding or paid invoices.
b) I have another table that holds a reference to the orders (tabped) in the previous table, which has the fields 'invoice number' and 'status' of orders that are part of a paid invoice or not. The reference between the two tables is the 'invoice number', which are not indices by nature.
c) Finally, if there is a 'write-down' in the pay tab, I need to flag the pay status on the tab page.
I need to make sure that all the records in the table tabs will be downloaded if there is a shortage in the tabs, changing both the status field code to 'downloaded'.
But I can not allow the sum of the requests linked to a certain invoice to not 'hit' the value indicated in the tabs, if inadvertently someone might have 'stirred' the tabped for any reason.
I would use for this a function written in plpgsql.
However, as I understand it, I can not use ROLLBACK within a function, nor SAVEPOINT, if, for any reason, the 'financial volume' of the orders indicated in the table of tabs does not correspond to the sum of the values of the requests. >
That is, if the values differ, the update of the tabs needs to be aborted.
Is there a 'rollback' or do I have to do an IF THEN ELSE structure to test values before the update?