Re: Re: REPLACE INTO table a la mySQL

Jan Wieck wrote:
>
> 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.

The only issue I have with your conclusion about DB schema is that REPLACE is
not part of standard SQL, so we do not need be too concerned. Just give them a
REPLACE trigger and be done with it. If that isn't good enough, in the FAQ, say
that the standard way is insert or update.