DELETE triggers in MySQL

Introduction

A DELETE trigger automatically runs a set of actions when a row is removed from a table. It helps keep data consistent or perform extra tasks without manual work.

You want to log every time a record is deleted from a table.

You need to update related tables when a row is removed.

You want to prevent deletion under certain conditions.

You want to archive deleted data automatically.

You want to maintain audit trails for data changes.

Syntax

MySQL

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

Triggers can run BEFORE or AFTER the DELETE action.

Use OLD.column_name to access the deleted row's data inside the trigger.

Examples

This trigger runs before a row is deleted from the employees table.

MySQL

CREATE TRIGGER before_delete_employee
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  -- actions before deleting an employee
END;

This trigger logs deleted orders after they are removed.

MySQL

CREATE TRIGGER after_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO deleted_orders_log(order_id, deleted_at) VALUES (OLD.id, NOW());
END;

Sample Program

This example creates a trigger that logs deleted employees into another table with the deletion time.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employee_deletions (
  emp_id INT,
  deleted_on DATETIME
);

DELIMITER $$
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_deletions(emp_id, deleted_on) VALUES (OLD.id, NOW());
END$$
DELIMITER ;

INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob');

DELETE FROM employees WHERE id = 1;

SELECT * FROM employee_deletions;

Important Notes

Remember to change the DELIMITER when creating triggers in MySQL to avoid syntax errors.

Use OLD to refer to the row being deleted inside the trigger.

Triggers cannot call COMMIT or ROLLBACK statements.

Summary

DELETE triggers run automatically when rows are deleted.

They can run BEFORE or AFTER the deletion.

Use them to log, prevent, or react to deletions.