How to Create a BEFORE DELETE Trigger in MySQL

In MySQL, you create a BEFORE DELETE trigger using the CREATE TRIGGER statement followed by the trigger name, timing (BEFORE), event (DELETE), and the table it applies to. Inside the trigger body, you can define actions to run before a row is deleted.

Syntax

The syntax to create a BEFORE DELETE trigger in MySQL includes naming the trigger, specifying the timing as BEFORE, the event as DELETE, and the target table. The trigger body contains the SQL statements to execute before the delete happens.

• CREATE TRIGGER trigger_name: Names the trigger.
• BEFORE DELETE ON table_name: Sets the trigger to run before a delete on the specified table.
• FOR EACH ROW: Runs the trigger for each row affected.
• BEGIN ... END: Contains the SQL code to execute.

sql

CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END

Example

This example creates a BEFORE DELETE trigger on a table named employees. It prevents deletion if the employee's role is 'Manager' by raising an error.

sql

DELIMITER $$
CREATE TRIGGER before_delete_employee
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  IF OLD.role = 'Manager' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete a Manager record';
  END IF;
END$$
DELIMITER ;

Common Pitfalls

Common mistakes when creating BEFORE DELETE triggers include:

• Not using DELIMITER to change the statement delimiter, causing syntax errors.
• Forgetting FOR EACH ROW, which is required for row-level triggers.
• Trying to modify the table being deleted from inside the trigger, which can cause recursion or errors.
• Not handling errors properly when preventing deletes.

sql

/* Wrong: Missing DELIMITER change causes error */
CREATE TRIGGER bad_trigger
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  IF OLD.role = 'Manager' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete a Manager';
  END IF;
END;

/* Right: Use DELIMITER to define trigger */
DELIMITER $$
CREATE TRIGGER good_trigger
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  IF OLD.role = 'Manager' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete a Manager';
  END IF;
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE TRIGGER trigger_name	Defines the trigger name
BEFORE DELETE ON table_name	Sets trigger timing and event
FOR EACH ROW	Runs trigger for each affected row
BEGIN ... END	Contains the SQL statements to execute
OLD.column_name	References the row data before deletion
SIGNAL SQLSTATE	Raises an error to prevent delete

Key Takeaways

Use CREATE TRIGGER with BEFORE DELETE to run code before a row is deleted.

Always include FOR EACH ROW for row-level triggers.

Use DELIMITER to define triggers with multiple statements.

Use OLD to access the row data before deletion inside the trigger.

Use SIGNAL to raise errors and prevent unwanted deletes.