How can I override the MySQL DELETE command to update a field rather than to remove the row?

Question

I wonder whether it's possible to override the functionality of MySQL's DELETE command to update a field on the selected row rather than to delete it completely.

This would make it possible to set a flag, for example deleted, from 0 / null to 1, without using UPDATE.

Is it possible to do, and if so how?

Answer 1

No, this isn't possible. You can block deletions with triggers or (where appropriate) foreign key constraints, but you can't make DELETE do anything other than delete.

It isn't more semantically correct -- you are updating a row, not deleting a row. DELETE means remove 0 or more rows.

If you really want to use the word "delete," create a stored procedure with an appropriate name that accepts the primary key as its argument.

To prevent deletions entirely, use a simple trigger like this:

CREATE TRIGGER t1_bd
BEFORE DELETE ON t1 -- table name
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'table t1 does not support deletion';

This will immediately terminate the query and prevent the deletion.

The customary DELIMITER statements and BEGIN/END block are not required when a trigger contains a single non-complex statement in its body, like the one above.

SQLSTATE 45000 means unhandled user-defined exception.

Answer 2

In databases you usually want to make an action on unwanted events, so for example you are able to rollback insert if data is incorrect, but table constraints do allow such data.

I would recommend using simple BEFORE DELETE trigger:

DELIMITER $$

CREATE TRIGGER before_tabName_row_delete
BEFORE DELETE ON tabName
FOR EACH ROW
BEGIN
    -- action, for example
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deleting from tabName is forbidden!';
END$$

DELIMITER ;

In MySQL we can use SIGNAL, here is manual for it, where second block of code may tell you a bit more: https://dev.mysql.com/doc/refman/8.0/en/signal.html
45000 code stands for "unknown error" or "not handled".
When you write some bigger triggers you should be sure to handle properly all known (for your case) exceptions to avoid "some strange things in database", but here 45000 is fine, but also you could use defined by yourself error code consistent to whole database (for example to use 42000 for deletion prevention, 42001 for update prevention and 42002 for insert prevention etc.).

Usually database has its own system for these operations, so for example Oracle and MySQL have OLD.attribute and NEW.attribute values for triggers that use FOR EACH ROW. These values may be overwritten, or the whole transaction may be rollbacked by simple ROLLBACK or raising exception.

Usually it is better to raise exception with its code and description so user (and some logging system' admin) will know what happened ;).