MySQL BEFORE DELETE Trigger

The MySQL can create a BEFORE DELETE trigger to automatically execute a set of SQL statements before a row or rows are deleted from a table. This trigger allows you to implement custom logic or constraints before the deletion operation takes place. Here’s the basic syntax for creating a BEFORE DELETE trigger:

DELIMITER //
CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;
//
DELIMITER ;

Here’s an explanation of each part of the trigger definition:

• trigger_name: Choose a unique name for your trigger.
• BEFORE DELETE: This specifies that the trigger will be executed before a DELETE operation on the table.
• table_name: Replace this with the name of the table on which the trigger should be defined.
• FOR EACH ROW: This indicates that the trigger will be executed for each row affected by the DELETE operation.
• BEGIN and END: These enclose the trigger logic, which consists of one or more SQL statements.

Within the trigger logic, you can reference the old values of the row being deleted using the OLD keyword. For example, you can compare the values in the row being deleted with other data or use it to perform certain actions.

Here’s an example of a BEFORE DELETE trigger that prevents the deletion of rows in a hypothetical orders table if the order’s status is set to “Shipped”:

DELIMITER //
CREATE TRIGGER prevent_delete_shipped_orders
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status = 'Shipped' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete shipped orders';
    END IF;
END;
//
DELIMITER ;

In the above trigger, if someone tries to delete a row with a status of “Shipped” in the orders table, the trigger will raise an error and prevent the deletion.

Remember to exercise caution when using BEFORE DELETE triggers, as they can significantly impact database operations. Always test your triggers thoroughly and ensure they behave as expected in your specific use case.