BEFORE DELETE and AFTER DELETE Triggers

DELETE triggers fire when rows are removed from a table. Only the OLD pseudo-row is available (there is no NEW for a delete). These triggers are typically used for archiving, cascade cleanup, and audit logging.

BEFORE DELETE Use Cases

Prevent deletion of rows that meet certain conditions
Copy the row to an archive table before it is removed
Check referential integrity manually when FK constraints are not used

AFTER DELETE Use Cases

Update aggregate counts in a summary table
Log the deletion with the old values for audit purposes
Cascade delete related rows in tables without FK constraints

Using a BEFORE DELETE trigger to archive rows is a safer alternative to soft deletes for scenarios where you want physical deletion but still need a record of what was removed.

Example

DELIMITER //

CREATE TRIGGER before_order_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    -- Archive the order before deleting
    INSERT INTO orders_archive
        (order_id, customer_id, total, status, archived_at)
    VALUES
        (OLD.id, OLD.customer_id, OLD.total, OLD.status, NOW());
END //

CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE customers
    SET total_orders = total_orders - 1
    WHERE id = OLD.customer_id;
END //

DELIMITER ;

SQL

Full Editor

Pro Tip

Archive rows in BEFORE DELETE so you always capture the data before it is gone.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned