Triggers in SQL

A trigger is a block of procedural logic that the database runs automatically in response to a data event — an INSERT, UPDATE, or DELETE on a table. You do not call a trigger; it fires on its own whenever the matching event happens. Triggers are how you enforce rules, maintain derived data, or write audit logs without depending on every application to remember to do so.

When can a trigger fire?

Two dimensions define a trigger:

1. Timing — BEFORE the event (you can inspect and modify the incoming data, or reject it) or AFTER the event (the change is done; you react to it).
2. Event — INSERT, UPDATE, or DELETE (some databases allow combinations).

Most triggers also run per row (FOR EACH ROW) — once for every affected row — which is the common, portable form.

A practical example: an audit log

A classic, genuinely useful trigger records who changed what. Whenever a student record is updated, we log the old and new name into an audit table.

PostgreSQL (trigger calls a trigger function):

-- PostgreSQL: triggers run a separate trigger FUNCTION

-- 1. Audit table
CREATE TABLE student_audit (
    audit_id    SERIAL PRIMARY KEY,
    student_id  INTEGER,
    old_name    VARCHAR(120),
    new_name    VARCHAR(120),
    changed_at  TIMESTAMP DEFAULT now()
);

-- 2. The function that does the work
CREATE OR REPLACE FUNCTION log_student_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- OLD = the row before the update; NEW = the row after
    INSERT INTO student_audit (student_id, old_name, new_name)
    VALUES (OLD.student_id, OLD.full_name, NEW.full_name);
    RETURN NEW;   -- required for BEFORE/AFTER row triggers
END;
$$;

-- 3. The trigger that wires the function to the event
CREATE TRIGGER trg_student_update
AFTER UPDATE ON student
FOR EACH ROW
EXECUTE FUNCTION log_student_change();

MySQL (logic lives inside the trigger body directly):

-- MySQL: the body is inline, no separate function needed
DELIMITER //
CREATE TRIGGER trg_student_update
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_audit (student_id, old_name, new_name)
    VALUES (OLD.student_id, OLD.full_name, NEW.full_name);
END //
DELIMITER ;

Now any update to student — from the app, an admin tool, or a manual UPDATE — is logged automatically. That guarantee is the whole point of a trigger.

OLD and NEW

Inside a row-level trigger you get two special references:

• NEW — the incoming row values (available in INSERT and UPDATE).
• OLD — the previous row values (available in UPDATE and DELETE).

In a BEFORE INSERT/BEFORE UPDATE trigger you can even modify NEW to clean or default data before it is written:

-- PostgreSQL BEFORE trigger function: normalise the email to lowercase
CREATE OR REPLACE FUNCTION lowercase_email()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.email := lower(NEW.email);   -- changes what actually gets stored
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_lowercase_email
BEFORE INSERT OR UPDATE ON student
FOR EACH ROW
EXECUTE FUNCTION lowercase_email();

BEFORE vs AFTER — which to use

• Use BEFORE when you need to validate or transform the data going in (clean a value, reject an invalid one, fill a default).
• Use AFTER when you need to react to a completed change (write an audit row, update a summary table, notify another table). The row already exists, so AFTER is safe for logging.

Good uses for triggers

• Audit trails — log every change for compliance or debugging.
• Maintaining derived data — keep a running total or counter in sync automatically.
• Enforcing rules too complex for a CHECK — cross-table validations that constraints cannot express.

The case against overusing triggers

Triggers are powerful but easy to abuse:

• Hidden behaviour. A trigger fires invisibly. Someone debugging an UPDATE may have no idea a trigger also wrote three other rows. This "spooky action at a distance" makes systems hard to reason about.
• Performance. A row-level trigger runs once per affected row. A bulk update of a million rows fires the trigger a million times.
• Cascading triggers. A trigger that updates another table can fire that table's triggers, creating chains that are hard to trace and can even loop.
• Portability. Like stored procedures, trigger syntax is vendor-specific.

A good guideline: use triggers for things that must happen regardless of which client made the change (audit, integrity), and keep ordinary business logic in application code where it is visible and testable.

Common mistakes

• Forgetting triggers fire per row. Bulk operations multiply the cost. Test triggers against large batches, not just single rows.
• Putting heavy work in a trigger. Slow logic inside a trigger slows down every write to the table.
• Creating accidental cascades or loops. A trigger writing to a table that triggers back can spiral. Map out trigger dependencies.
• Using AFTER when you needed BEFORE. You cannot modify the incoming row in an AFTER trigger — the change is already done. Validation/transformation belongs in BEFORE.
• Relying on triggers for logic the app should own. Hidden business rules surprise the next developer. Reserve triggers for cross-cutting concerns.

FAQ

Can a trigger stop a change from happening? Yes — a BEFORE trigger can raise an error (or in PostgreSQL RETURN NULL to skip the row), which cancels the operation. This lets you enforce complex validation.

Are triggers part of standard SQL? The concept and basic syntax are standardised (SQL:1999 onward), but the procedural language inside differs by vendor — PL/pgSQL, MySQL's dialect, T-SQL, PL/SQL.

Should I use a trigger or a constraint? Prefer a constraint when the rule fits one (it is simpler and faster). Use a trigger only for logic constraints cannot express, such as cross-table checks or audit logging.

Keep learning

Triggers run the same kind of procedural logic as stored procedures, often complement constraints in SQL, and interact closely with transactions & ACID.

Want to know exactly when a trigger is the right tool — and when it is not? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.