How to write a trigger to abort delete in MYSQL?
Try something like this -
DELIMITER $$
CREATE TRIGGER trigger1
BEFORE DELETE
ON table1
FOR EACH ROW
BEGIN
IF OLD.id = 1 THEN -- Abort when trying to remove this record
CALL cannot_delete_error; -- raise an error to prevent deleting from the table
END IF;
END
$$
DELIMITER ;
Improving @Devart's (accepted) answer with @MathewFoscarini's comment about MySQL SIGNAL Command, instead of raising an error by calling an inexistent procedure you could signal your custom error message.
DELIMITER $$
CREATE TRIGGER DeviceCatalog_PreventDeletion
BEFORE DELETE ON DeviceCatalog
FOR EACH ROW
BEGIN
IF old.id IN (1,2) THEN -- Will only abort deletion for specified IDs
SIGNAL SQLSTATE '45000' -- "unhandled user-defined exception"
-- Here comes your custom error message that will be returned by MySQL
SET MESSAGE_TEXT = 'This record is sacred! You are not allowed to remove it!!';
END IF;
END
$$
DELIMITER ;
The SQLSTATE 45000 was chosen as MySQL's Reference Manual suggests:
To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”
This way your custom message will be shown to the user whenever it tries to delete records ID 1
or 2
. Also, if no records should be deleted from the table, you could just remove the IF .. THEN
and END IF;
lines. This would prevent ANY records from being deleted on the table.