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

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?

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.

StackExchange Database Administrators Q#159670, answer score: 8