Re: AW: Re: Re: REPLACE INTO table a la mySQL

Zeugswetter Andreas SB wrote:
>
> > I think that application people would probably prefer the delete trigger,
> > insert trigger. It makes more sense, because I would interpret replace
> > as "get rid of the old if it exists" and "put in a new item". If people
> > wanted
> > to make sure code is run on delete, and they have to put it into a
> > delete trigger and a replace trigger, it would be two places for them.
> >
> > Frankly, I'm not sure why this is being seen as a weak approach.
> > My indended semantic was atomic delete (ignoring error) and insert.
>
> Adding another trigger event "replace" is imho not acceptable, since
> people guarding their data integrity with standards defined triggers
> for insert update and delete would open the door to inconsistency
> because they have not defined a replace trigger.
>
> Fire the delete then the insert trigger is imho not a straightforward answer,
> since a second possible interpretation would be to fire eighter the insert trigger
> or the update trigger if a row already existed.

Imho the second one is also the only correct one, as the definition of
REPLACE INTO
is "update if the row is there, else insert". The problem is just that
the test must
not fire any triggers and that test+(insert|update) must be atomic and
must fire the
respective trigger for insert|update. This just implies that it can't be
done by
simple rewrite, not that it is undoable.

OTOH, I think that our non-transactional UNIQUE constraint
implementation is a bigger
problem than REPLACE INTO (i.e. one is BUG the other is ENCHANCEMENT).

-----------------
Hannu