MySQL BEFORE DELETE Trigger

Master MySQL BEFORE DELETE triggers — archiving rows to a deleted_records table before they are removed, blocking deletion of protected rows via SIGNAL, capturing CURRENT_USER() and timestamps. Covers the TRUNCATE-bypasses-triggers gotcha and when to prefer soft deletes.

A BEFORE DELETE trigger fires just before a row is removed from the table. The row's data is still present at this point, accessible via the read-only OLD pseudo-row. Two main use cases:

• Archive the row — copy it to a "deleted" or audit table so the data isn't lost
• Block the deletion — SIGNAL if the row shouldn't be deleted, perhaps because it's referenced elsewhere or marked as protected

BEFORE DELETE is read-only with respect to the row being deleted — there's no NEW (the row is being removed, no future version exists), and you can't modify OLD to change the deletion. You can only inspect, archive, or block.

The classic archive pattern: a main table plus an archive table that captures every deletion.

DROP TABLE IF EXISTS deleted_employees_log;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100),
    salary  DECIMAL(10, 2)
);

CREATE TABLE deleted_employees_log (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    employee_id  INT,
    name         VARCHAR(100),
    salary       DECIMAL(10, 2),
    deleted_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_by   VARCHAR(100)
);

Trigger that copies every deleted row to deleted_employees_log, capturing both the data and the user who performed the deletion (via CURRENT_USER()):

DROP TRIGGER IF EXISTS before_delete_employee;

DELIMITER $$

CREATE TRIGGER before_delete_employee
    BEFORE DELETE ON employees
    FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees_log (employee_id, name, salary, deleted_by)
    VALUES (OLD.id, OLD.name, OLD.salary, CURRENT_USER());
END$$

DELIMITER ;

-- Seed
INSERT INTO employees (name, salary) VALUES
    ('John Doe',   50000),
    ('Jane Smith', 60000);

-- Verify before deletion
SELECT * FROM employees;

Now delete one and verify both tables:

DELETE FROM employees WHERE id = 2;

SELECT * FROM employees;
SELECT * FROM deleted_employees_log;

Jane Smith disappeared from employees but lives on in deleted_employees_log, complete with the timestamp and the user who performed the delete. The original record is gone but the data is preserved — auditable, restorable.

SIGNAL inside a BEFORE DELETE trigger blocks the deletion. Useful for protecting critical records from accidental removal:

DROP TRIGGER IF EXISTS prevent_high_salary_deletion;

DELIMITER $$

CREATE TRIGGER prevent_high_salary_deletion
    BEFORE DELETE ON employees
    FOR EACH ROW
BEGIN
    IF OLD.salary > 100000 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot delete employees with salary above 100,000 — requires manual approval';
    END IF;
END$$

DELIMITER ;

INSERT INTO employees (name, salary) VALUES ('CEO', 250000);

DELETE FROM employees WHERE name = 'CEO';

The deletion was blocked. Note the trigger fires per row — a wider DELETE FROM employees that would touch the CEO row gets blocked entirely (the whole DELETE is rolled back when any row's trigger SIGNALs).

1. Use DROP TRIGGER IF EXISTS at the top of every deployment script.
2. For archive tables, capture not just OLD data but also the deleting user (CURRENT_USER()) and the timestamp (CURRENT_TIMESTAMP or default).
3. Be explicit about column lists in the INSERT — INSERT INTO archive (...) VALUES (OLD.col1, OLD.col2, ...). Don't rely on positional matching that breaks when either schema changes.
4. Watch out for foreign keys. If employees has a foreign-key reference from another table and you don't have ON DELETE CASCADE, the actual DELETE will fail with FK violation — but only after your trigger has already fired, leaving a stranded archive entry. Either use CASCADE or check FK constraints in the trigger first.
5. Beware TRUNCATE. If audit completeness matters, document or programmatically prevent TRUNCATE on triggered tables.
6. Don't query the same table from inside the trigger — MySQL forbids it.
7. Consider soft deletes instead. Sometimes a deleted_at TIMESTAMP NULL column on the original table is simpler than a separate archive — no trigger needed, and you can still query "deleted" rows easily.

• BEFORE DELETE triggers fire just before a row is removed. Only OLD is available, read-only.
• Two main uses: archive the row to another table, and block the deletion with SIGNAL.
• You cannot modify OLD — the row is going away regardless.
• Capture CURRENT_USER() and a timestamp in archive logs for auditability.
• TRUNCATE bypasses triggers — relevant if audit completeness matters.
• For simpler retain-on-delete needs, consider soft deletes (a deleted_at column) instead of triggers and archive tables.