Triggers

A trigger runs automatically for each row affected by INSERT, UPDATE, or DELETE on a table, either BEFORE or AFTER the change.

Concept

A trigger runs automatically for each row affected by INSERT, UPDATE, or DELETE on a table, either BEFORE or AFTER the change. Inside it, NEW.col is the new row (INSERT/UPDATE) and OLD.col the old row (UPDATE/DELETE). MySQL supports only row-level triggers (no statement-level or INSTEAD OF triggers) and allows multiple triggers per event with FOLLOWS/PRECEDES ordering.

Why It Matters

Triggers enforce rules and side effects regardless of which application writes the data: audit trails, derived columns, complex validation, denormalized counters.

Syntax

DELIMITER //
CREATE TRIGGER [IF NOT EXISTS] trg_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON tbl
FOR EACH ROW
[{FOLLOWS | PRECEDES} other_trigger]
BEGIN
    ...
END //
DELIMITER ;

SHOW TRIGGERS FROM eh_company;
DROP TRIGGER IF EXISTS trg_name;

Example

21.1 BEFORE trigger: normalize and validate 21.2 AFTER trigger: audit log

CURRENT_USER() in a trigger returns the trigger's definer; use USER() for the connecting client account.

21.3 Maintaining derived data (With stock declared INT UNSIGNED, a result below zero fails with an out-of-range error, which aborts the INSERT — a built-in safety net.) 21.4 Rules and limitations

A trigger cannot modify the table that fired it with UPDATE/INSERT/DELETE (ERROR 1442) — use a BEFORE trigger and SET NEW.col = ... instead.

Triggers cannot use COMMIT/ROLLBACK, return result sets, or use dynamic SQL.

If a BEFORE trigger, the statement, or an AFTER trigger fails, the whole statement is rolled back (for InnoDB).

Triggers do not fire for cascaded foreign-key actions (a limitation to remember for audit logs), nor for TRUNCATE.

REPLACE fires DELETE + INSERT triggers; INSERT ... ON DUPLICATE KEY UPDATE fires BEFORE INSERT and then BEFORE/AFTER UPDATE when a duplicate is found.

Row-by-row execution slows bulk operations.

DELIMITER //
CREATE TRIGGER trg_customers_bi
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    SET NEW.email   = LOWER(TRIM(NEW.email));
    SET NEW.country = TRIM(NEW.country);
    IF NEW.email IS NOT NULL AND NEW.email NOT LIKE '%_@_%._%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';
    END IF;
END //
DELIMITER ;

INSERT INTO customers (customer_id, name, email, city, country)
VALUES (6, 'Farah Khan', '  [email protected] ', 'Hyderabad', 'India');
SELECT email FROM customers WHERE customer_id = 6;     -- [email protected]

CREATE TABLE salary_audit (
    audit_id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    emp_id      INT UNSIGNED   NOT NULL,
    old_salary  DECIMAL(10,2),
    new_salary  DECIMAL(10,2),
    operation   ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    changed_by  VARCHAR(288)   NOT NULL,
    changed_at  DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

DELIMITER //
CREATE TRIGGER trg_employees_au_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NOT (OLD.salary <=> NEW.salary) THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, operation, changed_by)
        VALUES (NEW.emp_id, OLD.salary, NEW.salary, 'UPDATE', CURRENT_USER());
    END IF;
END //

CREATE TRIGGER trg_employees_ad
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (emp_id, old_salary, new_salary, operation, changed_by)
    VALUES (OLD.emp_id, OLD.salary, NULL, 'DELETE', CURRENT_USER());
END //
DELIMITER ;

UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 2;
SELECT emp_id, old_salary, new_salary, operation FROM salary_audit ORDER BY audit_id;

+--------+------------+------------+-----------+
| emp_id | old_salary | new_salary | operation |
+--------+------------+------------+-----------+
|      4 |  120000.00 |  126000.00 | UPDATE    |
|      5 |   60000.00 |   63000.00 | UPDATE    |
+--------+------------+------------+-----------+

DELIMITER //
CREATE TRIGGER trg_order_items_ai
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products SET stock = stock - NEW.quantity WHERE product_id = NEW.product_id;
END //
DELIMITER ;

Expected Result

SELECT trigger_name, event_manipulation, action_timing, event_object_table
FROM information_schema.triggers WHERE trigger_schema = 'eh_company' ORDER BY trigger_name;

+-------------------------+--------------------+---------------+--------------------+
| TRIGGER_NAME            | EVENT_MANIPULATION | ACTION_TIMING | EVENT_OBJECT_TABLE |
+-------------------------+--------------------+---------------+--------------------+
| trg_customers_bi        | INSERT             | BEFORE        | customers          |
| trg_employees_ad        | DELETE             | AFTER         | employees          |
| trg_employees_au_salary | UPDATE             | AFTER         | employees          |
| trg_order_items_ai      | INSERT             | AFTER         | order_items        |
+-------------------------+--------------------+---------------+--------------------+

Common Mistakes

Trying to update the same table (ERROR 1442).

Expecting triggers on cascaded FK deletes.

Heavy logic in triggers slowing bulk loads.

Forgetting that CURRENT_USER() is the definer.

Best Practices

Use triggers for audit, normalization, and invariants — not business workflows.

Keep them short and documented; name them trg_<table>_<b|a><i|u|d>.

Prefer CHECK constraints and generated columns when they suffice.

Interview Relevance

Medium: "BEFORE vs AFTER?", "NEW vs OLD?", "Can a trigger modify its own table?", "Do triggers fire on cascading deletes?", "Statement-level triggers in MySQL?" (not supported).

Practice Questions

Prevent deleting DELIVERED orders with a BEFORE DELETE trigger.

Keep updated_at current without ON UPDATE CURRENT_TIMESTAMP.

Audit status changes on orders with old and new status.