Re: Re: REPLACE INTO table a la mySQL

"Jan Wieck" <JanWieck(at)Yahoo(dot)com> wrote in message
news:200106112044(dot)f5BKiwa03120(at)jupiter(dot)us(dot)greatbridge(dot)com(dot)(dot)(dot)
> mlw wrote:
> > Dale Johnson wrote:
> >
> > > "Jan Wieck" <JanWieck(at)Yahoo(dot)com> wrote in message
> > > news:200106061506(dot)f56F6dV01843(at)jupiter(dot)us(dot)greatbridge(dot)com(dot)(dot)(dot)
> > > > mlw wrote:
> > > > > [...]
> > > > > REPLACE into table set xx=yy, ww = zz where ID = fubar;
> > > > >
> > > > > A MUCH better solution!
> > > >
> > > > Please solve the trigger problem at least theoretical before
> > > > claiming that mySQL is that MUCH better. And please don't
> > > > solve it by ripping out trigger support :-)
> > > >
> > > for INSERT OR REPLACE into table ...
> > > if the record was not there, fire the insert trigger
> > > else
> > > delete the row (fire delete trigger)
> > > insert the new row (fire the insert trigger)
> > > fi
> > >
> > > semantically no other way, I think
> >
> > I'm not sure I agree. There are explicit triggers for update, insert,
and
> > delete, therefor why not also have a trigger for replace? It is one more
> > case. Rather than try to figure out how to map replace into two distinct
> > behaviors of insert or update based on some conditional logic, why not
just
> > have a replace trigger?
>
> Adding another trigger event type will break every existing
> DB schema that relies on custom triggers to ensure logical
> data integrity. Thus it is unacceptable as solution to
> support a non-standard feature - period.
>
> The question "does this row exist" can only be answered by
> looking at the primary key. Now BEFORE triggers are allowed
> to alter the key attributes, so the final primary key isn't
> known before they are executed.
>
> Thus the DELETE then INSERT semantic might be the only way.
> Pretty havy restriction, making the entire REPLACE INTO
> somewhat useless IMHO.
>

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.

Dale.