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
areallowed 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@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com