In PostgreSQL, I am not sure I understand after-triggers:
-
Within an
AFTER insert or update FOR EACH STATEMENTtrigger (function), is it correct that (contrary to e.g. MSSQL), if I query the trigger’s table, I see the data before any changes are applied to the table? -
And, if that is correct, how can I enforce a constraint over a whole table (for example, that the sum over some column does not exceed a certain value, or that at most N records have some boolean column set to true, etc.)?
The only approach I have been able to concoct is querying the table joining it with the deleted and inserted records in order to recover what the data would be after the statement, but I am new to PG and this seems a bit too much work for such a common requirement.
I have tried reading the docs as well as searching the web but I could not find a clear answer to the above, in fact not even an example.
Thanks for any clarification/advice.
In an AFTER trigger you already see the effects of the triggering statement.
But that won’t help you: you cannot reliably enforce a constraint over the whole table in a trigger because you don’t see uncommitted data from concurrent transactions. So there is always a race condition with using triggers to enforce such a constraint unless you use one of the following remedies
-
use the
SERIALIZABELisolation level -
use heavy locking to prevent concurrent transactions
See this article for a more detailed explanation.
3