Re: Re: REPLACE INTO table a la mySQL

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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com