
> 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.

Andreas

Wed, 20 Jun 2001 19:21:14 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
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

Mon, 25 Jun 2001 10:39:04 +0500
Hannu Krosing <hannu@tm.ee>
Re: AW: Re: Re: REPLACE INTO table  a la mySQL