# Triggers

> Maintain timestamps and audit changes with row-level and statement-level triggers.

---

LLMS index: [llms.txt](/en/llms.txt)

---

A *trigger* is a function that PostgreSQL calls automatically when a specific event happens on a table — `INSERT`, `UPDATE`, `DELETE`, or `TRUNCATE`, fired `BEFORE` or `AFTER` the row change, for each row or for the whole statement. Triggers are the standard tool for enforcing invariants the type system cannot, auditing changes, denormalizing for performance, and keeping `updated_at` timestamps current.

## What you'll learn {#learn}

- The `BEFORE` vs `AFTER` distinction
- Row-level (`FOR EACH ROW`) vs statement-level (`FOR EACH STATEMENT`)
- The `NEW` and `OLD` row variables
- A canonical example: auto-updating `updated_at`
- An audit-log trigger that writes to a side table

## Example {#example}

```sql {num="51.1" caption="Triggers"}
-- Setup
DROP TABLE IF EXISTS articles_example;
DROP TABLE IF EXISTS articles_audit;
CREATE TABLE articles_example (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  updated_at  timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE TABLE articles_audit (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  article_id  bigint NOT NULL,
  action      text   NOT NULL,
  by_user     text   NOT NULL DEFAULT current_user,
  at          timestamptz NOT NULL DEFAULT current_timestamp,
  old_row     jsonb,
  new_row     jsonb
);

-- Trigger function: bump updated_at before every UPDATE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := current_timestamp;
  RETURN NEW;
END;
$$;

CREATE TRIGGER articles_updated_at
BEFORE UPDATE ON articles_example
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Trigger function: write to the audit log after every change
CREATE OR REPLACE FUNCTION audit_article_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO articles_audit (article_id, action, old_row, new_row)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN NULL;  -- AFTER trigger return value is ignored
END;
$$;

CREATE TRIGGER articles_audit
AFTER INSERT OR UPDATE OR DELETE ON articles_example
FOR EACH ROW
EXECUTE FUNCTION audit_article_change();

-- Exercise the triggers
INSERT INTO articles_example (title, body) VALUES ('Hello', 'world');
UPDATE articles_example SET body = 'edited world' WHERE title = 'Hello';
DELETE FROM articles_example;

SELECT * FROM articles_audit ORDER BY id;

-- Cleanup
DROP TRIGGER IF EXISTS articles_updated_at ON articles_example;
DROP TRIGGER IF EXISTS articles_audit ON articles_example;
DROP FUNCTION IF EXISTS set_updated_at();
DROP FUNCTION IF EXISTS audit_article_change();
DROP TABLE IF EXISTS articles_example;
DROP TABLE IF EXISTS articles_audit;
```

A trigger is two pieces: a *trigger function* (returns `trigger`) and a *trigger* that ties the function to a table and an event. Inside the function:

- `NEW` is the new row (for `INSERT` and `UPDATE`).
- `OLD` is the old row (for `UPDATE` and `DELETE`).
- `TG_OP` is the operation (`'INSERT'`, `'UPDATE'`, `'DELETE'`, `'TRUNCATE'`).
- `TG_TABLE_NAME` etc. are also available for reusable triggers.

For `BEFORE ROW` INSERT/UPDATE triggers, return `NEW` (possibly modified); for DELETE, return `OLD`. Returning `NULL` skips that row's operation. For `AFTER ROW` triggers, the return value is ignored — the operation already happened. Statement-level triggers (`FOR EACH STATEMENT`) fire once per statement regardless of row count and do not have `NEW`/`OLD`.

The `updated_at` bumper is the most common trigger in the world. Define one trigger function, attach it to every table with an `updated_at` column.

## Run the example {#run}

To run:

```text
$ psql -f source/triggers.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
UPDATE 1
DELETE 1
 id | article_id | action  | by_user  |              at               | old_row | new_row
----+------------+---------+----------+-------------------------------+---------+---------
  1 |          1 | INSERT  | your_user | 2026-xx-xx ...                |         | {"id":1,...}
  2 |          1 | UPDATE  | your_user | 2026-xx-xx ...                | {...}   | {...}
  3 |          1 | DELETE  | your_user | 2026-xx-xx ...                | {...}   |
(3 rows)
 ...
```

## Common pitfalls {#pitfalls}

- A `BEFORE` trigger that returns `NULL` silently swallows the row — the `INSERT`/`UPDATE` is skipped without an error. Return `NEW` for INSERT/UPDATE and `OLD` for DELETE unless you mean to skip the row.
- Triggers fire inside the same transaction as the triggering statement. Heavy logic in a trigger slows every modification of the table.
- Triggers do not fire on `COPY ... FROM` by default? They do, actually — but `TRUNCATE` does not fire row-level triggers (only statement-level). Plan accordingly.
- Putting business logic in triggers makes it hard to discover from application code. Many teams limit triggers to *infrastructure* concerns (updated_at, audit) and keep business logic in the application.

> [!TIP] Tip
> Use `pg_trigger_depth()` inside a trigger to detect recursion. A trigger that updates the same table that fired it can loop — guard with `IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF;`.

> [!NOTE] Try it
> The script drops its demo tables and trigger functions at the end. Recreate the timestamp trigger function from the example before continuing. Add an `updated_at` column to `fruits` and attach the trigger from above. Update a row and confirm `updated_at` changes. Then add a `BEFORE INSERT` trigger that uppercases `name` automatically, and see it in action.

## Downloads {#downloads}

SQL file: [triggers.sql](/examples/source/triggers.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)