Hi all,
is it possible to avoid deleting of a row when a DELETE query is
executed? I want to UPDATE row (setting for example a boolean field)
insted of deleting it.

I can do this in Oracle and Postgres (with INSTEAD OF rule). Is it
possible to do this in MySQL?

Thank you in advance.

mario.cartia@gmail.com wrote:
> Hi all,
> is it possible to avoid deleting of a row when a DELETE query is
> executed? I want to UPDATE row (setting for example a boolean field)
> insted of deleting it.
>
> I can do this in Oracle and Postgres (with INSTEAD OF rule). Is it
> possible to do this in MySQL?

I don't think so. MySQL triggers can do other operations in addition to
the named operation (for example, inserting to a log table when an
operation is performed on a given table), and BEFORE INSERT/UPDATE
triggers can modify values entered into a row, but there's no way to
tell it to skip the operation.

A typical usage of INSTEAD OF, in addition to the usage you're
describing, is to allow non-updateable views to be made updateable, by
providing custom logic in the trigger to update the base tables of the
view. However, MySQL docs explicitly say that one cannot associate a
trigger with a view or a temporary table.

It sounds like you're doing someting akin to overloading the DELETE
statement, to make it do something other than its assigned semantics.
SQL is not an object-oriented language, and does not support overloading
in this way. I think you need to use UPDATE when you mean UPDATE, as in
this case.

Regards,
Bill K.