Re: Re: REPLACE INTO table a la mySQL

Jan Wieck wrote:
> 
> mlw wrote:
> > Dale Johnson wrote:
> >
> > > "Jan Wieck" <JanWieck@Yahoo.com> wrote in message
> > > news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com...
> > > > 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.