MySQL AFTER INSERT Trigger

Master MySQL AFTER INSERT triggers — accessing the auto-generated AUTO_INCREMENT ID via NEW.id, audit logging, maintaining summary tables with INSERT...ON DUPLICATE KEY UPDATE, and the BEFORE-vs-AFTER decision. Covers summary-table drift and the case for live VIEWs over trigger-maintained aggregates.

An AFTER INSERT trigger fires after a row has been written to the table. By the time the trigger runs, the new row is already in place and any auto-generated columns (AUTO_INCREMENT IDs, default timestamps, computed columns) have their final values. The NEW pseudo-row reflects exactly what's now stored.

The big difference from BEFORE INSERT: NEW is read-only here. You can't modify what was stored — that ship has sailed. AFTER INSERT triggers are for downstream work:

• Audit logging — record the insertion in a separate table
• Maintain summary tables — increment counts, update totals
• Cascade to related tables — auto-create related records
• Capture auto-generated values — write the just-assigned ID somewhere else

DROP TABLE IF EXISTS employee_log;
DROP TABLE IF EXISTS employees;

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

CREATE TABLE employee_log (
    log_id        INT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT,
    employee_name VARCHAR(100),
    inserted_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    inserted_by   VARCHAR(100)
);

Trigger that records every insertion to employee_log, capturing the auto-generated id that BEFORE INSERT couldn't have seen:

DROP TRIGGER IF EXISTS after_employee_insert;

DELIMITER $$

CREATE TRIGGER after_employee_insert
    AFTER INSERT ON employees
    FOR EACH ROW
BEGIN
    INSERT INTO employee_log (employee_id, employee_name, inserted_by)
    VALUES (NEW.id, NEW.name, CURRENT_USER());
END$$

DELIMITER ;

INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00);

SELECT * FROM employee_log;

Notice employee_id = 1 — the AUTO_INCREMENT value MySQL assigned. A BEFORE INSERT trigger couldn't have captured this; the ID hadn't been generated yet.

Keep a department headcount table in sync. Each new employee bumps the count for their department:

DROP TABLE IF EXISTS department_counts;

CREATE TABLE department_counts (
    department    VARCHAR(50) PRIMARY KEY,
    employee_count INT NOT NULL DEFAULT 0
);

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

DROP TRIGGER IF EXISTS after_employee_insert;

DELIMITER $$

CREATE TRIGGER after_employee_insert
    AFTER INSERT ON employees
    FOR EACH ROW
BEGIN
    -- Insert if not present, increment if present
    INSERT INTO department_counts (department, employee_count)
    VALUES (NEW.department, 1)
    ON DUPLICATE KEY UPDATE
        employee_count = employee_count + 1;
END$$

DELIMITER ;

INSERT INTO employees (name, salary, department) VALUES
    ('Alice',   45000, 'Engineering'),
    ('Bob',     50000, 'Engineering'),
    ('Charlie', 55000, 'Sales');

SELECT * FROM department_counts;

The department_counts table stays in sync without application code knowing it exists. INSERT ... ON DUPLICATE KEY UPDATE handles the "create or increment" logic in one statement.

1. Use DROP TRIGGER IF EXISTS at the top of every deployment script.
2. Reach for AFTER INSERT when you need the AUTO_INCREMENT ID or any other auto-assigned value — those are unavailable in BEFORE INSERT.
3. Capture CURRENT_USER() and a timestamp in audit logs.
4. Be careful with summary tables — they can drift if not all paths use the trigger. Periodically rebuild from the source data, or prefer a live VIEW.
5. Keep trigger work small. Per-row overhead multiplies with bulk inserts.
6. For bulk loads, consider dropping or disabling triggers temporarily, then rebuilding any derived data afterwards.
7. Don't INSERT into the same table the trigger fires on — would cause infinite recursion; MySQL forbids it.

• AFTER INSERT triggers fire after the new row is stored. Only NEW is available, read-only.
• Auto-generated values are visible in NEW — AUTO_INCREMENT IDs, defaulted timestamps, etc. — making AFTER INSERT the right choice when you need them.
• Common uses: audit logging, summary-table maintenance, cascade to related tables, capture auto-IDs for downstream work.
• Use BEFORE INSERT when you need to modify NEW or reject the insert; use AFTER INSERT for downstream work that depends on the row being stored.
• Trigger-maintained summary tables can drift — plan for periodic rebuild, or use a VIEW for always-live aggregates.