Tom Lane wrote:
>
> Matt Doucleff <matt@digitalfountain.com> writes:
> > I want a trigger to happen after constraint checking but before row
> > insertion.
>
> In other words, you'd like your trigger to be able to violate the
> constraints?
>
> This does *not* strike me as a good idea.
>
> > 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.
>
> Seems to me this is all irrelevant, because in either case, the first
> action will be rolled back if the second one fails. There might be
> some marginal amount of time wasted, which you could minimize by
> arranging things so that the more-likely-to-fail action is done first.
> But I see no correctness issue.
>
> regards, tom lane
Tom,
My trigger is guaranteed to never change the row's data. It is a "const trigger." I use it solely for side-effects.
Thusmy goal is not to violate constraints. My goal is to invoke the side-effect only if the data satisfies the
constraints. However, if my side-effect action fails, I still want the ability to abort the transaction. However, I
seewhy an "InBetween" trigger is dangerous in general.
For example, say I want to create a file when a row is inserted, and modify that file when the row is modified. If a
transactionfails, I need to undo my changes to the filesystem. I am not interested in modifying row data from a
trigger,but don't want to create the file if the new row's data violates constraints.
You are right regarding the inevitability of rollback due to subsequent operations failing. And I understand why the
trigger'stiming is just an optimization. Thus, my question becomes, how can I get a "rollback trigger" and undo the
side-effectwhen subsequent operations fail? I.e., if two rows are inserted in a transaction and I create the first
filebut the second row violates constraints, I need to be able to remove the new file during rollback.
Thank you for you help,
Matt
