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

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