Trigger Basics: BEFORE, AFTER, INSERT, UPDATE and DELETE

Concept

A trigger is a block of code the database executes automatically when a row is inserted, updated or deleted. It cannot be called directly and it cannot be skipped - every write through every client fires it.

Two dimensions give six trigger points:

Syntax

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- NEW.column  the incoming value  (INSERT, UPDATE)
    -- OLD.column  the previous value  (UPDATE, DELETE)
END$$

DELIMITER ;

Example: an audit trail

CREATE TABLE salary_audit (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT           NOT NULL,
    old_salary  DECIMAL(10,2),
    new_salary  DECIMAL(10,2),
    changed_by  VARCHAR(100),
    changed_at  DATETIME NOT NULL
);

DELIMITER $$

CREATE TRIGGER trg_employees_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_by, changed_at)
        VALUES (NEW.id, OLD.salary, NEW.salary, CURRENT_USER(), NOW());
    END IF;
END$$

DELIMITER ;

The IF matters: without it, every update to any column writes an audit row claiming the salary changed. Compare OLD and NEW for the specific column you are auditing. Note that <> is UNKNOWN when either side is NULL, so nullable columns need NEW.col <=> OLD.col (MySQL) or an IS DISTINCT FROM comparison.

Example: validation and normalisation

DELIMITER $$

CREATE TRIGGER trg_employees_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Normalise: store emails lower case and trimmed
    SET NEW.email = LOWER(TRIM(NEW.email));

    -- Validate: reject impossible data outright
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;

    -- Default: fill a value the caller omitted
    IF NEW.hire_date IS NULL THEN
        SET NEW.hire_date = CURDATE();
    END IF;
END$$

DELIMITER ;

SET NEW.column = ... only works in a BEFORE trigger. By the time an AFTER trigger runs, the row is already written and NEW is read only.

Example: maintaining a cached count

DELIMITER $$

CREATE TRIGGER trg_order_items_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders SET item_count = item_count + 1 WHERE id = NEW.order_id;
END$$

CREATE TRIGGER trg_order_items_after_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders SET item_count = item_count - 1 WHERE id = OLD.order_id;
END$$

DELIMITER ;

Cached aggregates need a trigger for every event that can change them - insert, delete, and update if the key can move. Miss one and the cache drifts silently, which is why the denormalisation note insists on a reconciliation query.

Managing triggers

DROP TRIGGER IF EXISTS trg_employees_salary_audit;
SHOW TRIGGERS;                                          -- MySQL
SELECT trigger_name, event_manipulation, action_timing
FROM   information_schema.triggers
WHERE  trigger_schema = DATABASE();                      -- standard

Important rules

• FOR EACH ROW means the trigger fires once per affected row, not once per statement.
• Only BEFORE triggers may modify NEW.
• TRUNCATE does not fire DELETE triggers. Neither, in most engines, do foreign key cascades.
• A trigger runs inside the caller's transaction; if the trigger fails, the whole statement is rolled back.
• MySQL before 5.7.2 allowed only one trigger per timing and event per table; later versions allow several, with FOLLOWS/PRECEDES controlling order.
• SQLite supports triggers; SQL Server also has INSTEAD OF triggers, which MySQL does not.

Common mistakes

• Auditing without comparing OLD and NEW, producing a row for every update.
• Comparing nullable columns with <> and missing changes involving NULL.
• Trying to SET NEW.col in an AFTER trigger.
• Assuming a cascade or a TRUNCATE will fire the audit trigger.
• Writing to the same table the trigger is on - MySQL rejects it to prevent recursion.

Best practices

• Keep trigger bodies short. Long triggers make every write slower and every bug harder to find.
• Name them so the table, timing and event are visible: trg_employees_before_insert.
• Use BEFORE for validation and normalisation, AFTER for logging and propagation.
• Compare with a NULL safe operator when the column is nullable.
• Document every trigger in the schema notes - invisible behaviour is the main complaint against them.

Practice

1. Write an audit trigger recording every status change on orders.
2. Write a BEFORE INSERT trigger that rejects an order total below zero.
3. Which events must a trigger set cover to keep orders.item_count correct?