I have a complex data validation requirement that spans many rows and possibly more than one table.
The application must be able to perform several data manipulation statements that could invalidate the requirement between one another, and only have the database check this requirement at transaction commit time.
Ideally I would have some sort of after trigger that is deferred to commit time, have it search for invalid or missing records and raise errors if any is found.
If I'm reading the manual correctly, the only kind of trigger that can be deferred to commit time is a constraint trigger. The problem is that this trigger must be declared for each row, but I need to only perform the validation once per transaction, because it executes complex queries.
What is the best way to address this issue?
Is there any other way, other than a constraint trigger, to defer execution of a piece of code at transaction commit time?
Otherwise, can I check for repeated invocations of my trigger function in the same transaction and return early on the second and following ones? I could do that by creating a temporary table on commit drop, but it seems overkill. Is there a lighter solution? -Tobia
