DELETE trigger in SQL

Introduction

A DELETE trigger automatically runs some actions right after or before a row is deleted from a table. It helps keep data safe or update related information without doing it manually.

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

You need to update another table when a row is removed.

You want to prevent deletion if certain conditions are not met.

You want to clean up related data in other tables automatically.

You want to keep track of who deleted data and when.

Syntax

SQL

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

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

Use BEFORE DELETE to run the trigger before the row is deleted.

Use AFTER DELETE to run the trigger after the row is deleted.

Examples

This trigger logs the ID and time whenever an employee is deleted.

SQL

CREATE TRIGGER log_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO deleted_log(employee_id, deleted_at)
  VALUES (OLD.id, CURRENT_TIMESTAMP);
END;

This trigger stops deletion of orders that are already shipped.

SQL

CREATE TRIGGER prevent_delete
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;

Sample Program

This example creates two tables and a DELETE trigger. When an employee is deleted, the trigger logs the employee's ID and deletion time into deleted_log. Then it deletes employee with ID 1 and shows the log.

SQL

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

CREATE TABLE deleted_log (
  employee_id INT,
  deleted_at TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER log_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO deleted_log(employee_id, deleted_at)
  VALUES (OLD.id, CURRENT_TIMESTAMP);
END$$
DELIMITER ;

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

DELETE FROM employees WHERE id = 1;

SELECT * FROM deleted_log;

Important Notes

In triggers, OLD refers to the row before deletion.

Not all databases support triggers exactly the same way; check your database docs.

Use triggers carefully to avoid slowing down your database operations.

Summary

DELETE triggers run automatically when rows are deleted.

They help automate tasks like logging or preventing unwanted deletes.

Use BEFORE DELETE or AFTER DELETE depending on when you want the trigger to run.