Re: ON ERROR triggers

On Mon, Jan 07, 2002 at 11:14:29AM -0800, Mikheev, Vadim wrote:
> I'd rather ask another question -:) How about constraints in DEFERRED mode?
> Looks like deferred mode allows you to do everything you need - ie make ALL
> required changes and then check everything when mode changed to immediate.
> Also note that this would be more flexible then trigger approach - you can
> change mode of individual constraint.
> 
> Two glitches though:
> 1. I believe that currently transaction will be aborted on first error
>    encountered, without checking all other changes for constraint
> violations.

That's the problem.

>    I suppose this can be easily changed for your needs. And user would just
>    point out what behaviour is required.

I suppose changing this is what i'm doing with my proposed error
handlers ;-) For error reporting there is no difference between
DEFERRED and IMMEDIATE. The only advantage DEFERRED provides and for
what it what added to the SQL standard is some pseudo-errors do not
arise.

> 2. Not sure about CHECK constraints but Uniq/PrimaryKey ones are not
>    deferrable currently -:( And this is muuuuuch worse drawback then absence
>    of comprehensive reports. It's more complex thing to do than on error
>    triggers but someday it will be implemented because of this is "must
> have"
>    kind of things.

A simple implementation of deferred UNIQUE constraints could be very
easily provided bases on my error handlers. Imagine a deferred UNIQUE
index where a DUPKEY is up to be inserted. When the DUPKEY appears in
DEFERRED mode my error handler will:

1) not mark the transaction for rollback
2) add a trigger to the deferred trigger queue to do checks on the DUPKEY  in the given index
3) that's all

Maybe not the most efficient way, but a very clean implementation
based on error handlers. Maybe now a little bit convinced of error
handlers ? Would be glad.

-- 
Holger Krug
hkrug@rationalizer.com