AlgoInsight

Q&A

What are triggers in SQL?

By AlgoInsight Team2026-06-07databasessqlsql

Overview

A trigger is "event-driven" SQL: you attach logic to a table that the database runs whenever a specified change happens. Because the database enforces it, the logic can't be bypassed by forgetful application code. Triggers are powerful for auditing and integrity but should be used judiciously - they execute invisibly, which can make systems harder to reason about.

graph TD
    A[INSERT / UPDATE / DELETE on table] --> B{Trigger timing}
    B --> C[BEFORE: validate / modify incoming row]
    B --> D[AFTER: audit / cascade / update other tables]
    C & D --> E[Trigger body runs automatically]
    style E fill:#1e1b4b,color:#fff

Trigger Components

Example (MySQL): Auditing changes

CREATE TRIGGER audit_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END;

Whenever a row in employees is updated, this automatically logs the old and new salary. OLD and NEW are pseudo-records exposing the row values before/after the change.

OLD and NEW

Inside row-level triggers you reference the affected row's values:

Event OLD available NEW available
INSERT No Yes (the inserted row)
UPDATE Yes (before) Yes (after)
DELETE Yes (the deleted row) No

Example (BEFORE trigger): Validation / normalization

CREATE TRIGGER normalize_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));   -- normalize before storing
END;

A BEFORE trigger can modify NEW values before they're written, or reject invalid data (e.g. raise an error).

Common Use Cases

Syntax Varies by Database

The concept is standard but syntax differs: MySQL uses OLD/NEW; PostgreSQL triggers call a separate trigger function (CREATE FUNCTION ... RETURNS TRIGGER then CREATE TRIGGER); SQL Server uses inserted/deleted pseudo-tables and is statement-level by default. Know your target database's flavor.

Drawbacks and Cautions

For these reasons, many teams prefer handling such logic in the application or in explicit stored procedures, reserving triggers for auditing and integrity that must be guaranteed at the data layer.

Why This Matters in Interviews

Database interviews use triggers to test knowledge of event-driven SQL and integrity. Cover the event types (INSERT/UPDATE/DELETE), timing (BEFORE/AFTER, plus INSTEAD OF for views), row-vs-statement level, and OLD/NEW access. Equally important is judgment: name the legitimate uses (auditing, validation, derived data) and the downsides (hidden logic, performance, recursion). Acknowledging database-specific syntax differences shows real-world experience.

Common Pitfalls

References

More answers