How can I override the MySQL DELETE command to update a field rather than to remove the row?
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.