Re: Need "InBetween" (not just Before and After) Trigger

Tom Lane wrote:
>
> Matt Doucleff <matt(at)digitalfountain(dot)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. Thus my 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 see why 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 transaction fails, 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's timing is just an optimization. Thus, my question becomes, how can I get a "rollback trigger" and undo the side-effect when subsequent operations fail? I.e., if two rows are inserted in a transaction and I create the first file but the second row violates constraints, I need to be able to remove the new file during rollback.

Thank you for you help,

Matt