Skip to content

Triggers

Automate actions on row changes with BEFORE, AFTER, and INSTEAD OF triggers.

What you'll learn

  • What a trigger is and how it fires automatically
  • BEFORE vs AFTER triggers and when to use each
  • Trigger events: INSERT, UPDATE, DELETE
  • Using triggers for auditing and derived columns
  • Why triggers can be a double-edged sword

Concept

What Is a Trigger?

A trigger is a named procedure that the database executes automatically when a specific event (INSERT, UPDATE, or DELETE) occurs on a table. Unlike stored procedures that you call explicitly, triggers fire on their own — you do not invoke them in application code. They are attached to a table and an event.

BEFORE vs AFTER

  • BEFORE triggers run before the row is modified. They can validate data, modify the NEW row (e.g. set a default or computed column), or cancel the operation by raising an error. Use BEFORE for data transformation and validation.
  • AFTER triggers run after the row has been modified. They cannot change the row, but they can update other tables, write audit logs, or fire notifications. Use AFTER for side effects like auditing.

Trigger Events

A trigger can fire on one or more events: INSERT, UPDATE, or DELETE. For UPDATE, you can further restrict it to fire only when specific columns change: UPDATE OF price, stock. Inside the trigger, you can access the old and new row values via OLD and NEW (PostgreSQL/MySQL) or :OLD and :NEW (Oracle).

Auditing with Triggers

A common use case is an audit log: every time a row changes, insert a record into an audit table with the old values, the new values, who changed it, and when. This is implemented with an AFTER trigger on UPDATE and DELETE. It provides a full history of changes without changing application code.

The Double-Edged Sword

Triggers are powerful but dangerous. They run invisibly — application developers may not realize that an INSERT also updates three other tables. This makes debugging difficult, can cause performance issues (cascading triggers), and can lead to surprising behavior during bulk operations. Use triggers sparingly, document them well, and prefer explicit application logic when possible.

Example

              -- PostgreSQL: BEFORE trigger to set updated_at automatically
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- PostgreSQL: AFTER trigger for audit logging
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.price IS DISTINCT FROM OLD.price THEN
    INSERT INTO price_audit (product_id, old_price, new_price, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_price
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  EXECUTE FUNCTION log_price_change();
            

Two triggers: a BEFORE UPDATE trigger that auto-sets updated_at, and an AFTER UPDATE trigger that logs price changes to an audit table only when the price actually changed.

Try it

  • SQL Formatter

    Format trigger function and CREATE TRIGGER statements.

  • Code Diff

    Compare trigger definitions when modifying audit logic.

Common mistakes

The mistake

Creating cascading triggers that fire each other in a loop

The fix

Triggers on table A that update table B, which has a trigger that updates table A, can loop infinitely. Design triggers carefully and use conditions to break cycles.

The mistake

Using triggers for business logic that should be in application code

The fix

Triggers are invisible to application developers and hard to test. Use them for data-level concerns (timestamps, audit logs) and keep business rules in the application.

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors