Triggers

A trigger is a stored routine that runs automatically in response to a data event (INSERT, UPDATE, DELETE, and in some systems TRUNCATE or DDL/logon events) on a table or view.

Concept

A trigger is a stored routine that runs automatically in response to a data event (INSERT, UPDATE, DELETE, and in some systems TRUNCATE or DDL/logon events) on a table or view.

BEFORE, AFTER, INSTEAD OF (views; SQL Server tables) INSERT, UPDATE (optionally UPDATE OF column), DELETE, TRUNCATE (PostgreSQL)

Row-level (FOR EACH ROW: once per affected row) or statement-level (FOR EACH STATEMENT: once per statement) Access to data

Row-level: NEW / OLD (PostgreSQL, MySQL, SQLite, Oracle :NEW/:OLD); SQL Server: inserted / deleted pseudo-tables Yes (AFTER/FOR)

Yes (only; uses inserted/deleted sets) Yes (plus compound triggers)

Why It Matters

Triggers enforce rules and side effects at the database level, regardless of which application changes the data: audit logs, derived columns, complex validation, synchronization of denormalized data.

Syntax

NEW is available for INSERT and UPDATE; OLD is available for UPDATE and DELETE.

-- PostgreSQL: trigger function + trigger
CREATE OR REPLACE FUNCTION trg_fn() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    -- use NEW / OLD, TG_OP
    RETURN NEW;          -- BEFORE row trigger: returned row is stored (return NULL to skip)
END;
$$;
CREATE TRIGGER trg_name
{BEFORE | AFTER} {INSERT | UPDATE [OF col] | DELETE} ON table_name
FOR EACH {ROW | STATEMENT}
[WHEN (condition)]
EXECUTE FUNCTION trg_fn();

-- MySQL
CREATE TRIGGER trg_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- use NEW.col / OLD.col
END;

-- SQL Server
CREATE TRIGGER trg_name ON table_name
{AFTER | INSTEAD OF} {INSERT, UPDATE, DELETE}
AS
BEGIN
    SET NOCOUNT ON;
    -- use inserted / deleted tables
END;

Example

27.1 BEFORE trigger: maintain updated_at and normalize data

BEFORE triggers can modify the incoming row or reject it (raise an error). AFTER triggers see the final row and are used for side effects.

27.2 AFTER trigger: audit-log example To log only real changes, add a condition to the UPDATE branch or use WHEN (OLD.salary IS DISTINCT FROM NEW.salary) in a separate UPDATE trigger.

MySQL (one trigger per event):

SQL Server (statement-level; must handle multiple rows):

27.3 Validation trigger (reject invalid changes) Prefer CHECK constraints when the rule involves only the row's own columns and no comparison to old values; use triggers for rules needing OLD vs NEW or other tables.

27.4 Row-level vs statement-level triggers PostgreSQL 10+ statement-level triggers can also access transition tables (REFERENCING NEW TABLE AS new_rows) for efficient set-based processing.

27.5 INSTEAD OF trigger (make a view writable) 27.6 Trigger use cases Audit trails (who changed what, when).

Maintaining updated_at timestamps.

Enforcing complex business rules involving OLD vs NEW or multiple tables.

Maintaining denormalized/summary data (e.g., products.units_sold).

Soft-delete handling and history tables (though many DBMSs now offer temporal tables).

Making complex views updatable (INSTEAD OF).

27.7 Trigger drawbacks Hidden logic: developers reading application code do not see side effects; debugging is harder.

Performance: row-level triggers run for every row; bulk operations can slow dramatically.

Cascading effects: a trigger's DML can fire other triggers.

Ordering: multiple triggers on the same event have DBMS-specific ordering rules (PostgreSQL: alphabetical by name; MySQL: FOLLOWS/PRECEDES; SQL Server: sp_settriggerorder for first/last only).

Portability: syntax differs completely between DBMSs.

Bulk-load bypass: some bulk tools skip triggers by default (e.g., SQL Server BULK INSERT without FIRE_TRIGGERS), and TRUNCATE does not fire DELETE triggers.

Testing and migration complexity. 27.8 Trigger recursion concerns

A trigger that modifies its own table, or tables whose triggers modify the original table, can recurse:

Use BEFORE triggers to modify NEW directly instead of issuing a separate UPDATE.

PostgreSQL: check pg_trigger_depth() or use WHEN conditions; limit UPDATE OF column events.

SQL Server: RECURSIVE_TRIGGERS database option (off by default for direct recursion); nested triggers limited to 32 levels; check TRIGGER_NESTLEVEL().

MySQL: a trigger cannot modify the table that fired it (error 1442), which prevents direct recursion.

Oracle: "mutating table" error (ORA-04091) when a row trigger queries/modifies its own table; use compound triggers.

-- PostgreSQL
ALTER TABLE customers ADD COLUMN updated_at TIMESTAMP;

CREATE OR REPLACE FUNCTION trg_customers_before_write()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.email      := LOWER(TRIM(NEW.email));
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER customers_before_write
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION trg_customers_before_write();     -- PostgreSQL 11+; older: EXECUTE PROCEDURE

UPDATE customers SET email = '  [email protected] ' WHERE customer_id = 2;
SELECT email, updated_at FROM customers WHERE customer_id = 2;
-- [email protected] | 2026-09-29 10:42:13.52

-- MySQL
CREATE TRIGGER customers_before_update
BEFORE UPDATE ON customers
FOR EACH ROW
SET NEW.email = LOWER(TRIM(NEW.email)),
    NEW.updated_at = CURRENT_TIMESTAMP;

CREATE TABLE salary_audit (
    audit_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- MySQL: AUTO_INCREMENT; SQL Server: IDENTITY
    emp_id       INT           NOT NULL,
    old_salary   DECIMAL(10,2),
    new_salary   DECIMAL(10,2),
    operation    VARCHAR(10)   NOT NULL,
    changed_by   VARCHAR(100)  NOT NULL,
    changed_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION trg_salary_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, operation, changed_by)
        VALUES (NEW.emp_id, NULL, NEW.salary, 'INSERT', CURRENT_USER);
    ELSIF TG_OP = 'UPDATE' 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);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, operation, changed_by)
        VALUES (OLD.emp_id, OLD.salary, NULL, 'DELETE', CURRENT_USER);
    END IF;
    RETURN NULL;   -- return value of AFTER row triggers is ignored
END;
$$;

CREATE TRIGGER employees_salary_audit
AFTER INSERT OR DELETE OR UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION trg_salary_audit();

DELIMITER //
CREATE TRIGGER employees_salary_audit_upd
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 //
DELIMITER ;

CREATE TRIGGER trg_employees_salary_audit ON employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(salary)
    BEGIN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, operation, changed_by)
        SELECT i.emp_id, d.salary, i.salary, 'UPDATE', SUSER_SNAME()
        FROM inserted i
        JOIN deleted d ON d.emp_id = i.emp_id
        WHERE i.salary <> d.salary;
    END IF;
END;

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;

-- PostgreSQL: prevent salary cuts greater than 20%
CREATE OR REPLACE FUNCTION trg_check_salary_cut()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.salary < OLD.salary * 0.8 THEN
        RAISE EXCEPTION 'Salary cut for employee % exceeds 20%%', NEW.emp_id;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER employees_check_salary_cut
BEFORE UPDATE OF salary ON employees
FOR EACH ROW EXECUTE FUNCTION trg_check_salary_cut();

-- MySQL: SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...';
-- SQL Server: THROW 50010, '...', 1; (and the statement is rolled back)
-- Oracle: RAISE_APPLICATION_ERROR(-20010, '...');

-- PostgreSQL: statement-level trigger — runs once per statement, even if it changes 0 or 1,000,000 rows
CREATE OR REPLACE FUNCTION trg_orders_changed() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO change_events (table_name, operation, happened_at)
    VALUES (TG_TABLE_NAME, TG_OP, CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$;

CREATE TRIGGER orders_changed
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT EXECUTE FUNCTION trg_orders_changed();

-- PostgreSQL / SQL Server / Oracle / SQLite
CREATE VIEW v_order_customer AS
SELECT o.order_id, o.status, c.name AS customer_name
FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

-- PostgreSQL
CREATE OR REPLACE FUNCTION trg_v_order_customer_upd() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders SET status = NEW.status WHERE order_id = OLD.order_id;
    RETURN NEW;
END;
$$;

CREATE TRIGGER v_order_customer_upd
INSTEAD OF UPDATE ON v_order_customer
FOR EACH ROW EXECUTE FUNCTION trg_v_order_customer_upd();

UPDATE v_order_customer SET status = 'DELIVERED' WHERE order_id = 1003;

Expected Result

INSERT INTO employees (emp_id, first_name, last_name, email, dept_id, manager_id, job_title, salary, hire_date)
VALUES (9, 'Sara', 'Kapoor', '[email protected]', 3, 6, 'HR Executive', 55000, DATE '2026-09-01');

SELECT emp_id, old_salary, new_salary, operation FROM salary_audit WHERE emp_id = 9;

Common Mistakes

SQL Server triggers written as if they fire once per row (using SELECT @id = emp_id FROM inserted) — breaks with multi-row statements.

Heavy logic (external calls, large queries) inside triggers.

Using triggers for rules that a CHECK or FK constraint can enforce.

Forgetting that AFTER row triggers in PostgreSQL must still RETURN something (NULL is fine).

Not documenting triggers.

Best Practices

Use triggers sparingly and for database-level concerns (audit, integrity, timestamps).

Keep trigger bodies short and set-based.

Name triggers clearly (trg_<table>_<timing>_<event>).

Document all triggers in the schema documentation.

Test multi-row operations.

Interview Relevance

Medium: "What is a trigger?", "BEFORE vs AFTER?", "Row vs statement triggers?", "What are the disadvantages?", "How would you implement an audit log?", "What are inserted/deleted tables?", "What is the mutating table error?".

Practice Questions

Write a trigger that prevents deleting orders with status DELIVERED.

Write a trigger that decrements products.stock when an order item is inserted.

Write an audit trigger for status changes on orders.

Explain how you would prevent a trigger from firing recursively.

Rewrite a SQL Server trigger that assumes a single row so that it handles multi-row updates.