SQL Triggers: Automating Logic at the Database Layer

A trigger is a function that the database executes automatically when a specific event occurs on a table. Insert a row, and a trigger can log who inserted it. Update a price, and a trigger can record the old value. Delete a record, and a trigger can prevent the deletion or cascade it to related tables.

Triggers move logic into the database itself. This is powerful when you need guarantees that no application code path can bypass. It is also dangerous when overused, because the logic becomes invisible to developers reading application code.

Trigger anatomy

Every trigger has three parts: the event, the timing, and the function it calls.

CREATE OR REPLACE FUNCTION log_price_change()
RETURNS trigger AS $$
BEGIN
  INSERT INTO price_history (product_id, old_price, new_price, changed_at)
  VALUES (OLD.id, OLD.price, NEW.price, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_price_change
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

This trigger fires after an update to the price column on the products table, but only when the price actually changes. It inserts a record into price_history with both the old and new values.

BEFORE vs AFTER

BEFORE triggers run before the row is written. They can modify the values being inserted or updated by changing NEW, or they can cancel the operation by returning NULL.

CREATE OR REPLACE FUNCTION normalize_email()
RETURNS trigger AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_normalize_email
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION normalize_email();

Every email is lowercased and trimmed before it hits the table, regardless of which application or migration inserts the data.

AFTER triggers run after the row is committed to the table (but still within the same transaction). They cannot modify the row, but they can perform side effects like logging, notifications, or updating denormalized data.

Use BEFORE when you need to validate or transform data. Use AFTER when you need to react to data that is already written.

Row-level vs statement-level

FOR EACH ROW fires the trigger once per affected row. FOR EACH STATEMENT fires once per SQL statement, regardless of how many rows it touches.

CREATE TRIGGER trg_bulk_update_notify
  AFTER UPDATE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION notify_order_changes();

Statement-level triggers are useful for batch notifications. If a single UPDATE touches 500 rows, a statement-level trigger fires once instead of 500 times. However, statement-level triggers in PostgreSQL do not have access to OLD and NEW for individual rows (you can use transition tables for that).

Transition tables

PostgreSQL 10+ supports transition tables, which give statement-level triggers access to the full set of affected rows:

CREATE OR REPLACE FUNCTION audit_bulk_changes()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_count, changed_at)
  SELECT 'orders', TG_OP, count(*), now()
  FROM new_table;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_bulk
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE FUNCTION audit_bulk_changes();

Building an audit log

Audit logging is the most common use case for triggers. Here is a reusable pattern:

CREATE TABLE audit_log (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text        NOT NULL,
  row_id     bigint      NOT NULL,
  action     text        NOT NULL,
  old_data   jsonb,
  new_data   jsonb,
  changed_by text        DEFAULT current_user,
  changed_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, row_id, action, old_data)
    VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, row_id, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, OLD.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, row_id, action, new_data)
    VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

Attach it to any table:

CREATE TRIGGER trg_audit_products
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW
  EXECUTE FUNCTION audit_trigger_fn();

CREATE TRIGGER trg_audit_orders
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION audit_trigger_fn();

One function serves every table. The TG_TABLE_NAME and TG_OP variables tell you which table and which operation fired the trigger.

Common pitfalls

Infinite loops. A trigger on table A inserts into table B. A trigger on table B updates table A. Both fire each other endlessly. PostgreSQL detects some cycles and throws an error, but not all. Design trigger chains carefully and document them.

Hidden performance costs. A row-level AFTER INSERT trigger that runs a complex query against a large table will slow down every single insert. If you bulk-load 100,000 rows, that trigger fires 100,000 times. Profile your triggers with EXPLAIN ANALYZE and monitor query durations.

Invisible logic. Application developers may not know a trigger exists. A DELETE that silently writes to an audit table, fires a notification, and updates a summary view is doing a lot of invisible work. Document triggers in your schema migration files and mention them in code reviews.

Transaction coupling. Triggers run inside the same transaction as the triggering statement. If the trigger fails, the entire transaction rolls back. This is usually desirable for data integrity, but it means a bug in your audit trigger can block production writes.

When to use triggers

Triggers are the right choice when:

• Data integrity rules must be enforced regardless of the caller. If five different services insert into the same table, a trigger guarantees the rule applies everywhere.
• Audit logging must be tamper-proof. Application-level logging can be bypassed. Trigger-based logging cannot, as long as the application connects with restricted privileges.
• Denormalized columns need automatic updates. A last_modified_at timestamp, a full_name computed from first_name and last_name, or a search vector column.

When to avoid triggers

• Complex business logic. If the trigger needs to call external APIs, send emails, or make decisions that depend on application state, keep that logic in the application layer.
• High-throughput write paths. Every trigger adds latency to the write. For tables receiving thousands of inserts per second, measure the overhead carefully.
• Cross-database operations. Triggers cannot span databases or external services within a transaction.

Triggers are a sharp tool. Used for the right problems, specifically data integrity, auditing, and denormalization, they provide guarantees that application code alone cannot. Used for the wrong problems, they create a hidden second codebase that nobody remembers to maintain.