Triggers: audit logs, updated_at and rules

Part of the free LogicWiz SQL & Databases course, Module 15: Views, functions and security. The queries and practice run in the app on real Postgres.

A customer complains that ₹5,000 vanished from her balance. Nobody on the team admits to changing it, and there is no record. Kabir wants the database itself to note every balance change, whoever makes it.

You'll learn to

• Write a trigger function and attach it with CREATE TRIGGER
• Use OLD and NEW, and choose BEFORE or AFTER, row or statement
• Build an audit log, an updated_at column and a business rule

Learn

A trigger runs by itself

A trigger tells Postgres: when this event happens on this table, run this function. Nobody calls it; the INSERT, UPDATE or DELETE fires it.

It has two parts:

1. A trigger function: RETURNS trigger, written in PL/pgSQL.
2. CREATE TRIGGER, which ties the function to a table and an event.

Inside the function, NEW is the row as it will be after the change and OLD is the row as it was before.

What OLD and NEW hold

Row-level triggers (FOR EACH ROW) fire once per changed row and see OLD/NEW. Statement-level ones (FOR EACH STATEMENT) fire once per statement and see neither.

An audit log of balance changes

CREATE TABLE balance_audit (
  account_id   INT,
  old_balance  NUMERIC(12,2),
  new_balance  NUMERIC(12,2)
);

CREATE FUNCTION log_balance() RETURNS trigger
LANGUAGE plpgsql
AS $
BEGIN
  INSERT INTO balance_audit VALUES (OLD.account_id, OLD.balance, NEW.balance);
  RETURN NEW;
END;
$;

CREATE TRIGGER trg_balance_audit
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_balance();

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 4;

SELECT * FROM balance_audit;

Both updates were logged without anyone writing an INSERT into the audit table. UPDATE OF balance fires only when the SET list names the balance column. A real audit table also stores who (current_user) and when (now()).

BEFORE or AFTER?

• BEFORE triggers run before the row is saved. They can change NEW (fill a column, clean a value) or stop the change with RAISE EXCEPTION. They must RETURN NEW for the row to be saved.
• AFTER triggers run once the row is saved. Changing NEW does nothing now; they are for side effects: audit logs, updating a summary table.

Rule of thumb: fix or block the row → BEFORE. React to the row → AFTER.

updated_at, filled in automatically

ALTER TABLE menu_items ADD COLUMN updated_at TIMESTAMP;

CREATE FUNCTION touch() RETURNS trigger
LANGUAGE plpgsql
AS $
BEGIN
  NEW.updated_at := statement_timestamp();
  RETURN NEW;
END;
$;

CREATE TRIGGER trg_touch
BEFORE UPDATE ON menu_items
FOR EACH ROW
EXECUTE FUNCTION touch();

UPDATE menu_items SET price = 130 WHERE item_id = 4;

SELECT item_id, name, price, updated_at IS NOT NULL AS touched
FROM menu_items
WHERE restaurant_id = 2;

Only the Masala Dosa row got a time. now() works the same way; most apps write NEW.updated_at := now();.

Enforcing a rule: no payments to frozen accounts

CREATE FUNCTION block_frozen() RETURNS trigger
LANGUAGE plpgsql
AS $
BEGIN
  IF EXISTS (SELECT 1 FROM accounts
             WHERE account_id IN (NEW.from_acc, NEW.to_acc) AND status = 'frozen') THEN
    RAISE EXCEPTION 'Payment % blocked: account is frozen', NEW.txn_id;
  END IF;
  RETURN NEW;
END;
$;

CREATE TRIGGER trg_block_frozen
BEFORE INSERT ON upi_txns
FOR EACH ROW
EXECUTE FUNCTION block_frozen();

INSERT INTO upi_txns VALUES (21, 1, 8, 100.00, '2025-08-09 10:00', 'success', 'gift');

A CHECK constraint can only look at the row itself. This rule needs another table (accounts), so it needs a trigger.

In a BEFORE row trigger, RETURN NULL means skip this row. The INSERT reports success, yet nothing is saved, and no error tells you why. Always end a BEFORE trigger with RETURN NEW (or RETURN OLD for DELETE) unless skipping is what you want.

Use triggers with care

Triggers are invisible: someone reading UPDATE accounts ... has no idea three other tables changed too. Too many of them and the database becomes hard to debug and slow to write.

• Prefer a constraint when one can do the job: NOT NULL, CHECK, UNIQUE, FOREIGN KEY.
• Use triggers for what constraints can't do: audit logs, updated_at, rules that read other tables.
• Keep trigger functions short and give them clear names.

Exams describe a trigger as an ECA rule: Event (INSERT/UPDATE/DELETE), Condition (the IF or the WHEN clause), Action (the function body). They also ask the difference between row-level and statement-level triggers, and between BEFORE and AFTER.

In one line

A trigger runs a function on INSERT/UPDATE/DELETE: BEFORE to change or block the row (RETURN NEW), AFTER for side effects like audit logs.

Interview corner

What is a trigger, and when would you use one?

A trigger is a function the database runs automatically when a row is inserted, updated or deleted. Good uses: audit logs, filling updated_at, keeping a summary table in sync, and rules that span tables. Avoid using them for core business flows, because the logic is hidden from anyone reading the application code.

BEFORE vs AFTER, row vs statement triggers?

BEFORE triggers run before the row is written and can change NEW or reject the row; AFTER triggers run once it is written and are for side effects. A row-level trigger fires once per affected row with OLD/NEW; a statement-level trigger fires once per statement, even if it changes zero rows.

Trigger or constraint for a rule?

Use a constraint whenever one can express the rule (CHECK, UNIQUE, FOREIGN KEY, NOT NULL): it is declarative, fast and visible in the schema. Use a trigger only when the rule needs other rows or tables, such as "no payments to frozen accounts".