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
betweenone 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
recordsand 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
pertransaction, 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
secondand following ones? I could do that by creating a temporary table on commit drop, but it seems overkill. Is there
alighter solution?
-Tobia