Triggers, stored procedures and functions

A trigger is code that the database runs by itself when a table changes: before or after each INSERT, UPDATE or DELETE, once for every row or once for the whole statement. A function takes arguments and returns a value, so it can be called inside a query, a constraint or a trigger. A stored procedure returns no value that a query can use; it is run on its own with CALL, and in PostgreSQL only a procedure may COMMIT or ROLLBACK while it runs. SQLite has triggers but neither of the other two: its extra functions come from the program that opens the database. All three move logic out of the application into the database. That suits rules every writer must obey, such as an audit trail or a limit stored in another table, and it hurts when it hides business workflows that nobody sees run.

You can run the SQLite triggers below in the browser, where sql.js 1.14.2 provides SQLite 3.49.1. The PL/pgSQL triggers, functions and procedures are shown as the output psql printed against a new PostgreSQL 18.6 database, and the Python program ran on Python 3.14.8.

Triggers: before, after or instead of

A trigger is defined by its event (INSERT, UPDATE, DELETE, and in PostgreSQL also TRUNCATE), its timing and its level:

• BEFORE runs before the row is written. In PostgreSQL a row-level BEFORE trigger can change the new row, or skip it.
• AFTER runs after the change and sees the final row. It suits audit records and work that depends on the change having happened.
• INSTEAD OF works only on views: its statements run in place of the change, as in the views lesson.
• Row level (FOR EACH ROW) runs once per affected row; statement level (FOR EACH STATEMENT) runs once per statement, even when it changes no rows. SQLite has row-level triggers only.

Inside a row-level trigger, NEW is the row as it will be after an INSERT or UPDATE, and OLD the row as it was before an UPDATE or DELETE. An audit trail needs exactly those two values:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT NOT NULL,
  balance INTEGER NOT NULL          -- in paise
);
CREATE TABLE balance_audit (
  seq         INTEGER PRIMARY KEY,  -- the order the changes were made in
  account_id  INTEGER NOT NULL,
  action      TEXT NOT NULL,
  old_balance INTEGER,
  new_balance INTEGER
);

-- Fires once per updated row, after the change, and only if the balance moved.
CREATE TRIGGER audit_balance_update
AFTER UPDATE OF balance ON accounts
WHEN OLD.balance IS NOT NEW.balance
BEGIN
  INSERT INTO balance_audit (account_id, action, old_balance, new_balance)
  VALUES (OLD.id, 'update', OLD.balance, NEW.balance);
END;

CREATE TRIGGER audit_account_delete
AFTER DELETE ON accounts
BEGIN
  INSERT INTO balance_audit (account_id, action, old_balance, new_balance)
  VALUES (OLD.id, 'delete', OLD.balance, NULL);
END;

INSERT INTO accounts VALUES (1, 'Asha', 20000), (2, 'Ravi', 5000), (3, 'Meera', 0);
UPDATE accounts SET balance = balance + 1000;          -- one statement, three rows
UPDATE accounts SET holder = 'Ravi K' WHERE id = 2;   -- not a balance change
UPDATE accounts SET balance = balance WHERE id = 1;   -- same value: WHEN skips it
DELETE FROM accounts WHERE id = 3;
SELECT * FROM balance_audit ORDER BY seq;

┌─────┬────────────┬────────┬─────────────┬─────────────┐
│ seq │ account_id │ action │ old_balance │ new_balance │
├─────┼────────────┼────────┼─────────────┼─────────────┤
│ 1   │ 1          │ update │ 20000       │ 21000       │
│ 2   │ 2          │ update │ 5000        │ 6000        │
│ 3   │ 3          │ update │ 0           │ 1000        │
│ 4   │ 3          │ delete │ 1000        │             │
└─────┴────────────┴────────┴─────────────┴─────────────┘

• One UPDATE changed three rows and the trigger ran three times, writing three audit rows in the order of the updates.
• AFTER UPDATE OF balance fires only for updates that name the balance column, so the change of Ravi’s name left no trace.
• The WHEN clause skipped the update that set Asha’s balance to the value it already had. IS NOT is the right test there: unlike <>, it treats two NULLs as equal and a NULL against a number as a change.
• The delete has only an OLD row, so the audit stores no new balance.

A rule that a constraint cannot express

A CHECK (balance >= 0) constraint is the simplest way to forbid negative balances, and it is the better tool when a rule looks only at one row. But a CHECK cannot read other tables. Here each account may go below zero by the overdraft stored for it in another table, or not at all:

CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL);
CREATE TABLE overdrafts (account_id INTEGER PRIMARY KEY REFERENCES accounts (id), allowed INTEGER NOT NULL);
INSERT INTO accounts VALUES (1, 'Asha', 20000), (2, 'Ravi', 5000), (3, 'Meera', 3000);
INSERT INTO overdrafts VALUES (2, 10000);

-- A rule that needs another table, which a CHECK constraint cannot read.
CREATE TRIGGER no_overdraft
BEFORE UPDATE OF balance ON accounts
WHEN NEW.balance < -coalesce((SELECT allowed FROM overdrafts WHERE account_id = NEW.id), 0)
BEGIN
  SELECT RAISE(ABORT, 'balance below the allowed overdraft');
END;

UPDATE accounts SET balance = balance - 12000 WHERE id = 2;   -- -7000: inside Ravi's overdraft
UPDATE accounts SET balance = balance - 3500;                 -- Ravi would reach -10500
SELECT * FROM accounts ORDER BY id;

┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 20000   │
│ 2  │ Ravi   │ -7000   │
│ 3  │ Meera  │ 3000    │
└────┴────────┴─────────┘

Runtime error near line 15: balance below the allowed overdraft

Ravi’s first withdrawal took him to −7,000 paise, inside his overdraft of 10,000, so it went through. The second statement tried to take 3,500 paise from everyone: Asha’s and Meera’s rows were fine, Ravi’s would have reached −10,500, and RAISE(ABORT, …) stopped the statement. The final balances show what that means. Asha’s row was updated first, yet she still has 20,000 paise: an aborted statement is undone as a whole, including the rows it had already changed and anything the triggers had written.

Trigger functions in PostgreSQL

In PostgreSQL a trigger calls a trigger function, declared RETURNS trigger, usually written in PL/pgSQL. Its return value matters for a row-level BEFORE trigger: the row it returns is the row that is written, and NULL skips the row. The return value of a row-level AFTER trigger is ignored.

# Row-level triggers in PostgreSQL 18, written in PL/pgSQL.
from pg_session import psql

psql("""
CREATE TABLE accounts (id integer PRIMARY KEY, holder text NOT NULL, email text, balance integer NOT NULL);
CREATE TABLE audit (seq bigserial PRIMARY KEY, op text, account_id integer,
                    old_balance integer, new_balance integer, by_role text);

-- BEFORE ... FOR EACH ROW: may change NEW, or stop the statement with an error.
CREATE FUNCTION tidy_account() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  IF NEW.balance < 0 THEN
    RAISE EXCEPTION 'account %: balance % is below zero', NEW.id, NEW.balance;
  END IF;
  RETURN NEW;            -- the row to write, as changed here
END $$;
CREATE TRIGGER a_tidy BEFORE INSERT OR UPDATE ON accounts
  FOR EACH ROW EXECUTE FUNCTION tidy_account();

-- AFTER ... FOR EACH ROW: sees the final row; its return value is ignored.
CREATE FUNCTION audit_balance() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit (op, account_id, old_balance, new_balance, by_role)
  VALUES (TG_OP, coalesce(NEW.id, OLD.id), OLD.balance, NEW.balance, current_user);
  RETURN NULL;
END $$;
CREATE TRIGGER b_audit AFTER UPDATE OF balance OR DELETE ON accounts
  FOR EACH ROW EXECUTE FUNCTION audit_balance();

INSERT INTO accounts VALUES
  (1, 'Asha', '  Asha@Example.COM ', 20000), (2, 'Ravi', 'ravi@example.com', 5000), (3, 'Meera', NULL, 0);
SELECT id, email FROM accounts ORDER BY id;
UPDATE accounts SET balance = balance + 1000;
UPDATE accounts SET balance = balance - 9000 WHERE id = 2;
DELETE FROM accounts WHERE id = 3;
SELECT op, account_id, old_balance, new_balance, by_role FROM audit ORDER BY seq;

-- A BEFORE trigger that returns NULL skips the row: no error, just one row fewer.
CREATE TABLE readings (meter integer, reading integer);
CREATE FUNCTION skip_repeat() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM readings WHERE meter = NEW.meter AND reading = NEW.reading) THEN
    RETURN NULL;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER skip_repeat BEFORE INSERT ON readings FOR EACH ROW EXECUTE FUNCTION skip_repeat();
INSERT INTO readings VALUES (7, 1520), (7, 1520), (7, 1544);
""")

CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 3
 id |      email       
----+------------------
  1 | asha@example.com
  2 | ravi@example.com
  3 | 
(3 rows)

UPDATE 3
ERROR:  account 2: balance -3000 is below zero
CONTEXT:  PL/pgSQL function tidy_account() line 5 at RAISE
DELETE 1
   op   | account_id | old_balance | new_balance | by_role  
--------+------------+-------------+-------------+----------
 UPDATE |          1 |       20000 |       21000 | postgres
 UPDATE |          2 |        5000 |        6000 | postgres
 UPDATE |          3 |           0 |        1000 | postgres
 DELETE |          3 |        1000 |             | postgres
(4 rows)

CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 2

• a_tidy trimmed and lower-cased Asha’s email before it was stored; while the trigger exists, no writer can store the untidy form.
• The same function refused a balance below zero with RAISE EXCEPTION, and the statement left no audit rows, because a trigger runs inside the statement’s transaction and its work is rolled back with it.
• b_audit recorded each change with TG_OP (the event) and current_user, the role that made it.
• A BEFORE trigger always runs before the AFTER ones, so b_audit saw the tidied row. When several triggers have the same timing, level and event, PostgreSQL runs them in alphabetical order of their names, which is why prefixes such as a_ and b_ are a common way to fix their order.
• Three readings went in and INSERT 0 2 came back. The second reading repeated the first, and the BEFORE trigger saw the first one because a row-level BEFORE trigger sees the rows its statement has already written. It returned NULL, so the row vanished without an error. Silent skips are easy to miss; raise an error unless skipping really is the rule.

Row level or statement level: counting the calls

A row-level trigger is a function call per row, and each call does its own work: here, an INSERT into the audit table. EXPLAIN ANALYZE counts the calls. This table has both kinds of trigger, and one UPDATE changes 10,000 rows:

# One UPDATE of 10,000 rows: how often a row-level and a statement-level trigger run.
from pg_session import psql

psql("""
CREATE TABLE accounts (id integer PRIMARY KEY, balance integer NOT NULL);
CREATE TABLE audit (account_id integer, old_balance integer, new_balance integer);
CREATE TABLE batches (op text, rows_changed integer);

CREATE FUNCTION audit_row() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit VALUES (OLD.id, OLD.balance, NEW.balance);
  RETURN NULL;
END $$;
CREATE TRIGGER audit_row AFTER UPDATE ON accounts
  FOR EACH ROW EXECUTE FUNCTION audit_row();

-- A transition table holds every row the statement changed.
CREATE FUNCTION audit_batch() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO batches SELECT TG_OP, count(*) FROM changed;
  RETURN NULL;
END $$;
CREATE TRIGGER audit_batch AFTER UPDATE ON accounts
  REFERENCING NEW TABLE AS changed
  FOR EACH STATEMENT EXECUTE FUNCTION audit_batch();

INSERT INTO accounts SELECT g, 1000 FROM generate_series(1, 10000) AS g;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
  UPDATE accounts SET balance = balance + 1;
SELECT count(*) AS audit_rows FROM audit;
SELECT * FROM batches;
""")

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 10000
                        QUERY PLAN                         
-----------------------------------------------------------
 Update on accounts (actual rows=0.00 loops=1)
   ->  Seq Scan on accounts (actual rows=10000.00 loops=1)
 Trigger audit_batch: calls=1
 Trigger audit_row: calls=10000
(4 rows)

 audit_rows 
------------
      10000
(1 row)

   op   | rows_changed 
--------+--------------
 UPDATE |        10000
(1 row)

The row-level audit_row was called 10,000 times and wrote 10,000 audit rows. The statement-level audit_batch was called once. It still knew about every row, because REFERENCING NEW TABLE AS changed gave it a transition table holding all the rows the statement changed, which it counted in one query. When a trigger only needs the set of changes, a statement-level trigger with a transition table does in one call what a row-level trigger does in one call per row. Transition tables are available only to AFTER triggers on ordinary tables.

Functions and procedures in PostgreSQL

A function computes a value: it is called inside an expression, in a SELECT, a WHERE, a CHECK or a trigger, and it runs inside the statement that calls it. Its volatility tells the planner what it may assume. An IMMUTABLE function always returns the same result for the same arguments, a STABLE one at least within one statement, and a VOLATILE one, the default, may return anything and change data. A procedure is a statement of its own: you run it with CALL, it returns nothing a query can use (output parameters come back as one result row), and a PL/pgSQL procedure may end transactions while it works.

# A function is called inside a query; a procedure is run by CALL and may commit as it goes.
from pg_session import psql

psql("""
CREATE TABLE marks (roll integer PRIMARY KEY, total integer NOT NULL, grade text);
INSERT INTO marks (roll, total) SELECT g, 40 + (g * 7) % 60 FROM generate_series(1, 9) AS g;

CREATE FUNCTION grade_of(total integer) RETURNS text
  LANGUAGE sql IMMUTABLE
  RETURN CASE WHEN total >= 90 THEN 'O' WHEN total >= 75 THEN 'A' WHEN total >= 60 THEN 'B' ELSE 'C' END;
SELECT roll, total, grade_of(total) FROM marks WHERE roll <= 3 ORDER BY roll;

CREATE PROCEDURE grade_in_batches(batch integer) LANGUAGE plpgsql AS $$
DECLARE
  done integer;
BEGIN
  LOOP
    UPDATE marks SET grade = grade_of(total)
    WHERE roll IN (SELECT roll FROM marks WHERE grade IS NULL ORDER BY roll LIMIT batch);
    GET DIAGNOSTICS done = ROW_COUNT;
    EXIT WHEN done = 0;
    COMMIT;                -- this batch is kept even if a later one fails
    RAISE NOTICE 'batch committed: %', done;
  END LOOP;
END $$;

CALL grade_in_batches(4);
SELECT grade, count(*) FROM marks GROUP BY grade ORDER BY grade;

SELECT grade_in_batches(4);        -- a procedure is not a function

BEGIN;                             -- inside a transaction block, COMMIT is refused
UPDATE marks SET grade = NULL;
CALL grade_in_batches(4);
ROLLBACK;
""")

CREATE TABLE
INSERT 0 9
CREATE FUNCTION
 roll | total | grade_of 
------+-------+----------
    1 |    47 | C
    2 |    54 | C
    3 |    61 | B
(3 rows)

CREATE PROCEDURE
NOTICE:  batch committed: 4
NOTICE:  batch committed: 4
NOTICE:  batch committed: 1
CALL
 grade | count 
-------+-------
 A     |     3
 B     |     2
 C     |     3
 O     |     1
(4 rows)

ERROR:  grade_in_batches(integer) is a procedure
LINE 1: SELECT grade_in_batches(4);
               ^
HINT:  To call a procedure, use CALL.
BEGIN
UPDATE 9
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function grade_in_batches(integer) line 10 at COMMIT
ROLLBACK

• grade_of is an IMMUTABLE SQL function used like any built-in function. Because it is immutable, it could also be used in an index on grade_of(total).
• grade_in_batches updated four rows at a time and committed after each batch, so a failure in batch three would still keep the first eight grades. This is the usual reason to write a procedure: long maintenance work in chunks, each committed, so that locks are short and a failure loses only one chunk.
• SELECT grade_in_batches(4) was refused: a procedure is not a function. CALL on a function is refused the same way.
• Inside BEGIN … ROLLBACK the procedure’s COMMIT failed with “invalid transaction termination”. A procedure may control transactions only when CALL runs in a transaction of its own: not inside an explicit transaction block, and not when a query or a function call stands between it and the top-level CALL.

Interview questions often ask about cursors. A cursor is a query whose rows are read a few at a time, and in PL/pgSQL every FOR row IN SELECT … loop uses one. Normally a cursor closes when its transaction commits. A loop that commits inside a procedure keeps going because PostgreSQL turns its cursor into a holdable one at the first COMMIT: the rest of the query’s rows are computed there and then, and locks the query took are no longer held after it.

RETURNING old and new in PostgreSQL 18

Some triggers exist only to tell the application what a change did. PostgreSQL 18 lets RETURNING name the row before the change, old, and after it, new, in INSERT, UPDATE, DELETE and MERGE:

# PostgreSQL 18: RETURNING can show the row before (old) and after (new) a change.
from pg_session import psql

psql("""
CREATE TABLE accounts (id integer PRIMARY KEY, holder text NOT NULL, balance integer NOT NULL);
INSERT INTO accounts VALUES (1, 'Asha', 200), (2, 'Ravi', 80);

UPDATE accounts SET balance = balance - 50 WHERE id = 1
RETURNING id, old.balance AS before, new.balance AS after;

INSERT INTO accounts VALUES (2, 'Ravi', 120), (3, 'Meera', 40)
ON CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance
RETURNING id, old.balance AS before, new.balance AS after, old IS NULL AS inserted;

DELETE FROM accounts WHERE balance < 100
RETURNING old.id, old.holder, new.id IS NULL AS gone;

MERGE INTO accounts AS a
USING (VALUES (1, 25), (4, 500)) AS t(id, amount) ON a.id = t.id
WHEN MATCHED THEN UPDATE SET balance = a.balance + t.amount
WHEN NOT MATCHED THEN INSERT VALUES (t.id, 'new holder', t.amount)
RETURNING merge_action(), t.id, old.balance AS before, new.balance AS after;
""")

CREATE TABLE
INSERT 0 2
 id | before | after 
----+--------+-------
  1 |    200 |   150
(1 row)

UPDATE 1
 id | before | after | inserted 
----+--------+-------+----------
  2 |     80 |   120 | f
  3 |        |    40 | t
(2 rows)

INSERT 0 2
 id | holder | gone 
----+--------+------
  3 | Meera  | t
(1 row)

DELETE 1
 merge_action | id | before | after 
--------------+----+--------+-------
 UPDATE       |  1 |    150 |   175
 INSERT       |  4 |        |   500
(2 rows)

MERGE 2

The update returned both balances, 200 and 150. In the upsert, old IS NULL tells an inserted row from an updated one; in the delete, new is NULL; and MERGE adds merge_action() to say which branch each row took. When only the statement that makes the change needs to know, this replaces a trigger. When every writer must leave a record, whichever program it is, the audit trigger is still the tool.

SQLite: functions from the host program

SQLite runs inside your program, and that program can add SQL functions to it. Python’s sqlite3 module does this with create_function; the new function can then be called anywhere in SQL, triggers included:

"""SQLite has no stored procedures: the program that opens the database adds its own functions."""
import os
import sqlite3


def grade_of(total):
    if total is None:
        return None
    return "O" if total >= 90 else "A" if total >= 75 else "B" if total >= 60 else "C"


if os.path.exists("school.db"):
    os.remove("school.db")
app = sqlite3.connect("school.db")
# deterministic=True tells SQLite that the same input always gives the same output.
app.create_function("grade_of", 1, grade_of, deterministic=True)
app.executescript("""
    CREATE TABLE marks (roll INTEGER PRIMARY KEY, total INTEGER, grade TEXT);
    CREATE TRIGGER set_grade AFTER INSERT ON marks
    BEGIN
      UPDATE marks SET grade = grade_of(NEW.total) WHERE roll = NEW.roll;
    END;
""")
app.executemany("INSERT INTO marks (roll, total) VALUES (?, ?)", [(1, 47), (2, 93), (3, 61)])
app.commit()
for row in app.execute("SELECT roll, total, grade FROM marks ORDER BY roll"):
    print(row)

# A second program opens the same file without registering the function.
other = sqlite3.connect("school.db")
try:
    other.execute("INSERT INTO marks (roll, total) VALUES (4, 88)")
except sqlite3.OperationalError as e:
    print("other program:", e)
other.close()
app.close()
os.remove("school.db")

(1, 47, 'C')
(2, 93, 'O')
(3, 61, 'B')
other program: no such function: grade_of

The grades were set by a trigger that calls grade_of, a Python function. The catch is in the last line: a second program that opened the same file without registering the function could not insert at all, because the trigger fails with “no such function”. In SQLite, logic in the database still lives in the application code, and every program that writes must bring it.

When logic belongs in the database

A useful test is who must obey the rule. Code in the database runs for every program, script and console session that changes the data, which is its strength and its cost.

• Put it in the database when the rule is an invariant: an audit trail no writer may skip, a limit that depends on another table, a value derived from the row, or a check that must hold even for a hand-written UPDATE. Prefer a constraint when one can express the rule, and a trigger when only code can.
• Keep it in the application when it is a workflow: sending email, calling a payment service, applying business rules that change every month. A side effect outside the database cannot be rolled back with the transaction, and a slow call inside a trigger makes every write slow. A common pattern is a trigger, or the application, that writes a row into a table of pending messages, and a separate worker that sends them.
• Know the costs. Triggers are invisible in the statement that fires them, so their effects surprise readers of the code. A trigger whose SQL fires other triggers cascades, and PostgreSQL sets no limit on the depth: avoiding endless recursion is the author’s job. Row-level triggers multiply the work of large statements, 10,000 calls for one UPDATE above. They also need their own tests, and they make moving to another database harder.

Exercise: an audit trigger for fee changes

-- Write the trigger here: one fee_audit row for every change of a due amount.
CREATE TRIGGER fee_change
AFTER UPDATE ON fees
BEGIN
  SELECT 1;   -- replace this with an INSERT into fee_audit
END;

-- The checks run these statements. Leave them as they are.
UPDATE fees SET due = due - 5000 WHERE due >= 5000;
UPDATE fees SET due = due WHERE roll = 101;
UPDATE fees SET due = 0 WHERE roll = 103;
SELECT roll, old_due, new_due FROM fee_audit ORDER BY seq;

# Sample tests: each one creates the two tables with its own fees, runs your file (the trigger and the statements at
# its end) and compares the rows of fee_audit that the last line returns, in order.
tests:
  - name: two students pay 5000 paise, then roll 103 is cleared
    ordered: true
    setup: |
      CREATE TABLE fees (roll INTEGER PRIMARY KEY, due INTEGER NOT NULL);
      CREATE TABLE fee_audit (seq INTEGER PRIMARY KEY, roll INTEGER NOT NULL, old_due INTEGER, new_due INTEGER);
      INSERT INTO fees VALUES (101, 12000), (102, 3000), (103, 7000);
    expected:
      columns: [roll, old_due, new_due]
      rows:
        - [101, 12000, 7000]
        - [103, 7000, 2000]
        - [103, 2000, 0]
  - name: exactly 5000 is paid off, and an amount that stays 0 adds nothing
    ordered: true
    setup: |
      CREATE TABLE fees (roll INTEGER PRIMARY KEY, due INTEGER NOT NULL);
      CREATE TABLE fee_audit (seq INTEGER PRIMARY KEY, roll INTEGER NOT NULL, old_due INTEGER, new_due INTEGER);
      INSERT INTO fees VALUES (101, 5000), (102, 0), (103, 0);
    expected:
      columns: [roll, old_due, new_due]
      rows:
        - [101, 5000, 0]
  - name: an amount just under 5000 is left alone
    ordered: true
    setup: |
      CREATE TABLE fees (roll INTEGER PRIMARY KEY, due INTEGER NOT NULL);
      CREATE TABLE fee_audit (seq INTEGER PRIMARY KEY, roll INTEGER NOT NULL, old_due INTEGER, new_due INTEGER);
      INSERT INTO fees VALUES (103, 9000), (104, 4999);
    expected:
      columns: [roll, old_due, new_due]
      rows:
        - [103, 9000, 4000]
        - [103, 4000, 0]

Check yourself

Interview questions

What is the difference between a stored procedure and a function in PostgreSQL? (warm-up)

A function is called inside an expression, for example in a SELECT, a WHERE or a CHECK, and returns a value, a row or a set of rows; it runs inside the statement that calls it, so it can never commit. Its volatility, from IMMUTABLE to VOLATILE, tells the planner whether its result can be reused or indexed. A procedure, created with CREATE PROCEDURE, is run on its own with CALL and returns no value a query can use, only output parameters as one result row. A PL/pgSQL procedure may COMMIT or ROLLBACK while it runs, provided CALL runs in its own transaction, which makes it the tool for batch jobs that commit as they go. Calling a procedure with SELECT, or a function with CALL, is an error.

Key takeaways

• A trigger runs automatically on INSERT, UPDATE or DELETE: BEFORE can change or skip the row, AFTER sees the final row, INSTEAD OF replaces changes on views. NEW and OLD hold the row after and before.
• Row-level triggers run once per row, statement-level ones once per statement; PostgreSQL’s transition tables give an AFTER statement trigger every changed row in one call. SQLite has row-level triggers only.
• Triggers run inside the statement’s transaction: an error in either undoes both, even for rows changed earlier in the same statement.
• In PostgreSQL, functions are called inside queries and cannot commit; procedures run with CALL and may commit, but only outside an explicit transaction block. PostgreSQL 18’s RETURNING old and new replaces some triggers.
• SQLite has no stored procedures: functions come from the host program, and every program that writes must register them.
• Use the database for invariants every writer must obey, and the application for workflows and side effects.