11.3 Triggers: Power & Peril — The Complete SQL Course

Triggers are database routines that run automatically when a table changes. They are perfect for boring, local bookkeeping like updated_at and audit rows — and dangerous when they hide surprising work behind an ordinary INSERT or UPDATE.

In 11.2 you called functions and procedures directly. A trigger function runs because PostgreSQL calls it for you.

BEFORE row triggers: change the row being written

A trigger has two parts: a trigger function and a trigger that attaches it to a table event. Trigger functions return the special type trigger, and row triggers receive event-specific row images: INSERT has NEW, DELETE has OLD, and UPDATE has both.

This BEFORE UPDATE row trigger maintains updated_at. The timestamp is fixed only so the lesson output is deterministic; a production version usually uses the current clock.

CREATE TABLE menu_items (
  item_id integer PRIMARY KEY,
  item_name text NOT NULL UNIQUE,
  price numeric(5,2) NOT NULL CHECK (price >= 0),
  updated_at timestamp NOT NULL
);
 
INSERT INTO menu_items (item_id, item_name, price, updated_at)
VALUES
  (1, 'Latte', 4.50, TIMESTAMP '2026-07-01 08:00:00'),
  (2, 'Cookie', 3.00, TIMESTAMP '2026-07-01 08:00:00'),
  (3, 'Cold Brew', 4.50, TIMESTAMP '2026-07-01 08:00:00');
 
CREATE FUNCTION set_menu_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = TIMESTAMP '2026-07-05 09:00:00';
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER menu_items_set_updated_at
BEFORE UPDATE ON menu_items
FOR EACH ROW
EXECUTE FUNCTION set_menu_updated_at();
 
SELECT item_id, item_name, price, updated_at
FROM menu_items
WHERE item_name = 'Latte';
 
UPDATE menu_items
SET price = 4.75
WHERE item_name = 'Latte';
 
SELECT item_id, item_name, price, updated_at
FROM menu_items
WHERE item_name = 'Latte';

CREATE TABLE
INSERT 0 3
CREATE FUNCTION
CREATE TRIGGER
 item_id | item_name | price |     updated_at
---------+-----------+-------+---------------------
       1 | Latte     |  4.50 | 2026-07-01 08:00:00
(1 row)
UPDATE 1
 item_id | item_name | price |     updated_at
---------+-----------+-------+---------------------
       1 | Latte     |  4.75 | 2026-07-05 09:00:00
(1 row)

The rehearsal SELECT proves the one row the UPDATE will touch. The trigger changes NEW.updated_at before PostgreSQL stores the row, then RETURN NEW says "write this version."

AFTER triggers: react to what happened

BEFORE triggers can adjust or reject a row before it lands. AFTER triggers react after PostgreSQL has accepted the change. That makes them a natural fit for audit trails: the original write should happen first, then the audit row records what changed.

This next example uses two triggers on the same event. The row trigger runs once per changed row and writes audit rows. The statement trigger runs once for the whole UPDATE, no matter how many rows it touched.

CREATE TABLE menu_item_audit (
  audit_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  item_id integer NOT NULL,
  old_price numeric(5,2) NOT NULL,
  new_price numeric(5,2) NOT NULL,
  changed_at timestamp NOT NULL
);
 
CREATE FUNCTION audit_menu_price_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.price IS DISTINCT FROM NEW.price THEN
    INSERT INTO menu_item_audit (item_id, old_price, new_price, changed_at)
    VALUES (NEW.item_id, OLD.price, NEW.price, TIMESTAMP '2026-07-05 09:05:00');
 
    RAISE NOTICE 'audited price change for item %', NEW.item_id;
  END IF;
 
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER menu_items_price_audit
AFTER UPDATE OF price ON menu_items
FOR EACH ROW
EXECUTE FUNCTION audit_menu_price_change();
 
CREATE FUNCTION report_price_update_statement()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'price update statement finished';
  RETURN NULL;
END;
$$;
 
CREATE TRIGGER menu_items_price_statement
AFTER UPDATE OF price ON menu_items
FOR EACH STATEMENT
EXECUTE FUNCTION report_price_update_statement();
 
SELECT item_id, item_name, price
FROM menu_items
WHERE item_name IN ('Cookie', 'Cold Brew')
ORDER BY item_id;
 
UPDATE menu_items
SET price = price + 0.25
WHERE item_name IN ('Cookie', 'Cold Brew');
 
SELECT audit_id, item_id, old_price, new_price, changed_at
FROM menu_item_audit
ORDER BY audit_id;

CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
 item_id | item_name | price
---------+-----------+-------
       2 | Cookie    |  3.00
       3 | Cold Brew |  4.50
(2 rows)
NOTICE:  audited price change for item 2
NOTICE:  audited price change for item 3
NOTICE:  price update statement finished
UPDATE 2
 audit_id | item_id | old_price | new_price |     changed_at
----------+---------+-----------+-----------+---------------------
        1 |       2 |      3.00 |      3.25 | 2026-07-05 09:05:00
        2 |       3 |      4.50 |      4.75 | 2026-07-05 09:05:00
(2 rows)

Read the output carefully. The statement changed two menu rows, so the row trigger fired twice and inserted two audit rows. The statement trigger fired once and printed one notice. That is the beginner-level distinction:

FOR EACH ROW means once per affected row. The row images depend on the event: INSERT gets NEW, DELETE gets OLD, and UPDATE gets both.
FOR EACH STATEMENT means once per SQL statement, even if the statement touches zero, one, or many rows. It does not get one OLD or NEW value per changed row.

The trap: hidden trigger cascades

Now add one more trigger. When an audit row is inserted, it queues a manager review. The original user will still run only one visible UPDATE on menu_items.

CREATE TABLE audit_review_queue (
  review_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  audit_id integer NOT NULL REFERENCES menu_item_audit(audit_id),
  reason text NOT NULL
);
 
CREATE FUNCTION queue_audit_review()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_review_queue (audit_id, reason)
  VALUES (NEW.audit_id, 'price change review');
 
  RAISE NOTICE 'queued review for audit %', NEW.audit_id;
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER menu_audit_queue_review
AFTER INSERT ON menu_item_audit
FOR EACH ROW
EXECUTE FUNCTION queue_audit_review();
 
SELECT item_id, item_name, price
FROM menu_items
WHERE item_name = 'Latte';
 
UPDATE menu_items
SET price = 5.00
WHERE item_name = 'Latte';
 
SELECT (SELECT count(*) FROM menu_item_audit) AS audit_rows,
       (SELECT count(*) FROM audit_review_queue) AS review_rows;

CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
 item_id | item_name | price
---------+-----------+-------
       1 | Latte     |  4.75
(1 row)
NOTICE:  queued review for audit 3
NOTICE:  audited price change for item 1
NOTICE:  price update statement finished
UPDATE 1
 audit_rows | review_rows
------------+-------------
          3 |           1
(1 row)

The review notice appears first because the audit trigger's INSERT fires the queue trigger before the audit trigger function resumes and prints its own notice.

Rules of thumb: boring, visible, few

Triggers earn their keep when the rule is local to the table and should be enforced no matter which client writes the data. Good candidates are updated_at, immutable audit trails, simple derived row cleanup, and security-sensitive enforcement that must not depend on one application path.

Keep the team rules blunt:

Boring: a trigger should do one small table-adjacent job.
Visible: document triggers near the table definition and in migration review. Name them so the event and purpose are obvious.
Few: if a behavior needs a workflow diagram, it probably belongs in explicit application code, a procedure, or a job queue.

Also avoid unbounded recursion. PostgreSQL can fire a trigger because another trigger wrote another row. Sometimes that is exactly what you asked for; sometimes it is a production incident. This lesson's cascade stops after one extra insert by design.

sql — playgroundlive

CREATE TABLE menu_items (
item_id integer PRIMARY KEY,
item_name text NOT NULL UNIQUE,
price numeric(5,2) NOT NULL CHECK (price >= 0),
updated_at timestamp NOT NULL
);

INSERT INTO menu_items (item_id, item_name, price, updated_at)
VALUES
(1, 'Latte', 4.50, TIMESTAMP '2026-07-01 08:00:00'),
(2, 'Cookie', 3.00, TIMESTAMP '2026-07-01 08:00:00'),
(3, 'Cold Brew', 4.50, TIMESTAMP '2026-07-01 08:00:00');

CREATE FUNCTION set_menu_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = TIMESTAMP '2026-07-05 09:00:00';
RETURN NEW;
END;
$$;

CREATE TRIGGER menu_items_set_updated_at
BEFORE UPDATE ON menu_items
FOR EACH ROW
EXECUTE FUNCTION set_menu_updated_at();

SELECT item_id, item_name, price, updated_at
FROM menu_items
WHERE item_name = 'Latte';

UPDATE menu_items
SET price = 4.75
WHERE item_name = 'Latte';

SELECT item_id, item_name, price, updated_at
FROM menu_items
WHERE item_name = 'Latte';

-- break it: uncomment — trigger targets must call a RETURNS trigger function
-- CREATE FUNCTION not_a_trigger()
-- RETURNS text
-- LANGUAGE SQL
-- AS $$
--   SELECT 'nope';
-- $$;
-- CREATE TRIGGER broken_trigger
-- AFTER UPDATE ON menu_items
-- FOR EACH ROW
-- EXECUTE FUNCTION not_a_trigger();

⌘/Ctrl + Enter to run

Section 11 packaged behavior: views, functions, procedures, and triggers. Section 12 turns to the rules that decide whether a group of writes succeeds together, waits, conflicts, or rolls back: transactions and concurrency.

Checkpoint

Answer all three to mark this lesson complete

Triggers: Power & Peril

BEFORE row triggers: change the row being written

AFTER triggers: react to what happened

The trap: hidden trigger cascades

Rules of thumb: boring, visible, few

Checkpoint ✦

Checkpoint