6

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?

2
  • 2
    Why do you want to update without using UPDATE? Dec 31, 2016 at 17:50
  • @Michael-sqlbot: It seems more semantically correct to DELETE rather than to UPDATE. Dec 31, 2016 at 22:17

2 Answers 2

8

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.

0
0

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 ;).

1
  • Throwing an error prevents any code from executing where your comment is: --action, for example. BEFORE DELETE triggers cannot execute actions and prevent deleting (at least via signaling) unfortunately. It's effectively a transaction. Sep 12, 2021 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.