Knowledgeable Postgres hackers,
I want a trigger to happen after constraint checking but before row insertion.
insert_row
{
db_check_constraints();
if (constraints satisfied) {
attempt_action()
if (action_successful) {
return allow_insertion
}
}
return disallow_insertion
}
Doing the constraint checking in C is error prone and difficult to maintain.
That is Postgres' job anyway.
If I use a "before" trigger, the constraints are checked after I've already
performed my action. Thus I have performed my action yet no row is inserted
(which is inconsistent).
If I use an "after" trigger, the constraints are checked first. If the row is
inserted, I attempt to perform my action. If my action fails, then I have an
inserted row but no action, which is also inconsistent.
The only solution I've developed involves a duplicate table and a transaction.
BEGIN
insert into duplicate table with identical constraints
insert into real table with trigger
COMMIT
By inserting to the duplicate table without a trigger, I get constraint
checking performed by the db. Then I insert into the real table with the
"before" trigger. If my action fails, everything fails. Obviously I prefer a
solution not involving duplicate tables or such hacks.
Thank you,
Matt