MySQL AFTER DELETE Trigger

Master MySQL AFTER DELETE triggers — notification logging with proper CONCAT spacing, decrementing summary counts to keep aggregates in sync, manual cleanup of related data when foreign-key cascades aren't an option, and best practices for symmetric INSERT/DELETE trigger pairs.

An AFTER DELETE trigger fires after a row has been removed from the table. The OLD pseudo-row still has the deleted row's data — read-only — so you can see exactly what was removed and react.

The most common uses:

• Notification logging — record the deletion in a notification or summary table
• Cleanup of related data in tables that don't have FK ON DELETE CASCADE
• Decrement summary counts — keep aggregate tables in sync
• Cascade complex business actions beyond what FK cascades cover

DROP TABLE IF EXISTS order_logs;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_date  DATE
);

CREATE TABLE order_logs (
    log_id       INT AUTO_INCREMENT PRIMARY KEY,
    order_id     INT,
    log_message  VARCHAR(255),
    log_date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_by   VARCHAR(100)
);

INSERT INTO orders (order_id, customer_id, order_date) VALUES
    (1, 1001, '2024-04-10'),
    (2, 1002, '2024-04-12');

Trigger that records every order deletion in order_logs. Note the careful use of CONCAT_WS with a space separator — produces clean output even if you change the message format later:

DROP TRIGGER IF EXISTS after_delete_order_trigger;

DELIMITER $$

CREATE TRIGGER after_delete_order_trigger
    AFTER DELETE ON orders
    FOR EACH ROW
BEGIN
    DECLARE log_msg VARCHAR(255);

    SET log_msg = CONCAT('Order ', OLD.order_id, ' deleted (customer ',
                         OLD.customer_id, ')');

    INSERT INTO order_logs (order_id, log_message, deleted_by)
    VALUES (OLD.order_id, log_msg, CURRENT_USER());
END$$

DELIMITER ;

DELETE FROM orders WHERE order_id = 1;

SELECT * FROM orders;
SELECT * FROM order_logs;

If you maintained a count summary in an AFTER INSERT trigger (see the AFTER INSERT page), you almost certainly need the matching AFTER DELETE trigger to keep the counts honest:

CREATE TABLE IF NOT EXISTS customer_order_counts (
    customer_id   INT PRIMARY KEY,
    order_count   INT NOT NULL DEFAULT 0
);

DROP TRIGGER IF EXISTS after_order_delete;

DELIMITER $$

CREATE TRIGGER after_order_delete
    AFTER DELETE ON orders
    FOR EACH ROW
BEGIN
    UPDATE customer_order_counts
    SET    order_count = GREATEST(order_count - 1, 0)
    WHERE  customer_id = OLD.customer_id;
END$$

DELIMITER ;

The GREATEST(... - 1, 0) protects against the count going negative if the summary table somehow drifts.

If you can't or don't want to use foreign-key cascades, an AFTER DELETE trigger can clean up related rows manually:

CREATE TABLE IF NOT EXISTS order_items (
    item_id   INT AUTO_INCREMENT PRIMARY KEY,
    order_id  INT,
    product   VARCHAR(100),
    quantity  INT
);

DROP TRIGGER IF EXISTS cleanup_order_items;

DELIMITER $$

CREATE TRIGGER cleanup_order_items
    AFTER DELETE ON orders
    FOR EACH ROW
BEGIN
    DELETE FROM order_items WHERE order_id = OLD.order_id;
END$$

DELIMITER ;

1. Use DROP TRIGGER IF EXISTS at the top of every deployment script.
2. Pair AFTER DELETE with AFTER INSERT for symmetric maintenance — every count incremented by an INSERT trigger should be decremented by a DELETE trigger, otherwise summaries drift.
3. Watch your CONCAT spaces. A missing space produces "Order 1deleted" instead of "Order 1 deleted" — easy mistake. Use CONCAT_WS(' ', ...) for cleaner output.
4. Capture metadata — timestamp and CURRENT_USER() in audit/notification logs.
5. Prefer FK cascades over trigger-based manual cascades when possible.
6. Remember TRUNCATE bypasses triggers. If your AFTER DELETE trigger does important cleanup, document or programmatically prevent TRUNCATE on that table.
7. Don't DELETE from the same table the trigger fires on — MySQL forbids it.
8. For per-row work that scales with bulk DELETEs, measure performance before committing to a trigger-based approach.

• AFTER DELETE triggers fire after a row has been removed. Only OLD is available, read-only.
• Common uses: notification logging, summary count maintenance, cleanup of related data when FK cascades aren't an option.
• Pair with AFTER INSERT triggers for symmetric maintenance — counts and other aggregates need both.
• Prefer foreign-key ON DELETE CASCADE over trigger-based manual cleanup when the schema allows it.
• TRUNCATE bypasses DELETE triggers — beware if your trigger does critical work.
• Watch CONCAT spacing in log messages — missing spaces produce "Order 1deleted"-style typos.