In environments where data integrity is critical, such as billing or sales systems, simply assigning user permissions is not always enough: sometimes you need to directly prevent the deletion or modification of records in certain tables.
MySQL allows us to do this easily with triggers and the SIGNAL command. For example, suppose we have a Sales table where we store all sales. We want to prevent any record from being deleted. To achieve this, we can create a BEFORE DELETE trigger like this:
CREATE DEFINER=`root`@`%` TRIGGER `Venta_BEFORE_DELETE`
BEFORE DELETE ON `Venta`
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'MD: YOU CANNOT DELETE ANY ACCOUNT FROM THIS TABLE';
END;
What does each part of the Trigger do?
CREATE DEFINER='root'@'%' TRIGGER ...
Create a trigger with root user privileges for any host (%).BEFORE DELETE ON Venta
It is executed before a record is deleted from the Sales table.FOR EACH ROW
It fires once for each row that is being deleted.SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '…';
Throws a handled exception with SQLSTATE 45000 (user defined) and the error message ‘MD: CANNOT DELETE ANY ACCOUNTS FROM THIS TABLE.’.
With this block, every time someone tries to execute a DELETE FROM Venta… MySQL will stop the operation and return an error with your custom message:
It’s that simple to protect your critical tables without having to modify user permissions.