SQL Triggers

A trigger is a piece of code attached to a table that the database runs automatically whenever a row is inserted, updated, or deleted. Nobody calls it. The statement that changes the table fires it, and the trigger can check the change, alter it, refuse it, or write to other tables.

That is the appeal and the problem in one: a rule that always runs, and that nobody reading the UPDATE can see.

The parts of a trigger

• Event: INSERT, UPDATE (often of named columns only), or DELETE.
• Timing: BEFORE runs before the row is written and can change or reject it; AFTER runs once the change is made, typically to write somewhere else. INSTEAD OF replaces the change entirely, usually to make a view writable.
• Level: FOR EACH ROW runs once per changed row; a statement-level trigger runs once per statement, however many rows it touched.
• Old and new values: a row trigger sees the row before the change (OLD) and after it (NEW). SQL Server exposes them as the tables deleted and inserted instead.

An audit trigger in PostgreSQL

The classic use: every time an order's status changes, record the old and new values in a log table. In PostgreSQL the trigger calls a function:

create function log_status_change() returns trigger as $$
begin
  insert into order_status_log (order_id, old_status, new_status, changed_at)
  values (old.order_id, old.status, new.status, now());
  return new;
end;
$$ language plpgsql;

create trigger orders_status_audit
after update of status on orders
for each row
when (old.status is distinct from new.status)
execute function log_status_change();

After that, a plain update orders set status = 'shipped' ... writes the log row too, from any application, script, or console.

Across databases

Why triggers are used carefully

• They hide side effects. The statement says UPDATE orders. Nothing in it says that a log table, a stock count, and a search index also change. Someone debugging a wrong number has to know to look.
• Row triggers multiply. An update of 100,000 rows fires a row trigger 100,000 times, and a bulk load can slow down by far more than the trigger's own work suggests.
• They chain. A trigger that writes to another table can fire that table's triggers, and the order of several triggers on the same event differs by engine.
• They get switched off. Bulk loads and migrations often disable triggers for speed, and every row written in that window skips the rule.

Most teams keep triggers for rules that must hold no matter who writes to the table (audit trails are the common one) and keep business logic in code that can be read and tested.

DuckDB: no triggers

create trigger orders_status_audit
after update on orders
for each row
execute function log_status_change()

DuckDB stops at the second word: Parser Error: syntax error at or near "trigger". What a trigger would do, you do another way, and each way is visible in the schema or the script.

Rejecting bad rows: a constraint

A BEFORE trigger that raises an error on a bad value is a CHECK constraint. A trigger that fills in a missing value is a DEFAULT. A table of returns, with both:

create table returns (
    return_id integer primary key,
    order_id bigint not null,
    reason varchar not null
        check (reason in ('damaged', 'wrong item', 'changed mind')),
    received_at timestamp default current_timestamp
);

insert into returns (return_id, order_id, reason)
values (1, 3, 'damaged')

insert into returns (return_id, order_id, reason)
values (2, 3, 'broken')

Refused with Constraint Error: CHECK constraint failed on table returns with expression CHECK((reason IN ('damaged', 'wrong item', 'changed mind'))). The first row got its received_at without anyone supplying it. See table constraints; for a value computed from other columns in the row, see generated columns.

Writing to a log: an explicit script

An audit trigger becomes two statements in a transaction: write the log row, then make the change. Order 4 is processing:

create table order_status_log (
    order_id bigint,
    old_status varchar,
    new_status varchar,
    changed_at timestamp default current_timestamp
);

begin;

insert into order_status_log (order_id, old_status, new_status)
select order_id, status, 'shipped'
from orders
where order_id = 4;

update orders
set status = 'shipped'
where order_id = 4;

commit

select order_id, old_status, new_status
from order_status_log

One row: order 4, processing to shipped. The transaction makes the pair all-or-nothing, so the log cannot record a change that did not happen. The order of the two statements matters: run the UPDATE first and the log reads the status after it changed, recording shipped to shipped.

Catching what slipped through: a check query

Some rules span rows or tables, where a constraint cannot reach. Write them as queries that should return nothing, and run them after every load. The data quality checks lesson builds a set of them.

What can go wrong

• A script is only a rule if everyone uses it. A trigger fires for every writer; the explicit log above runs only when someone runs that script. A direct UPDATE from a console skips it. Keep writes to audited tables in one place.
• Logging the new value as the old one. Read the old value before the change, as above, or log both from a copy taken first.
• Porting a trigger-heavy schema. Moving from PostgreSQL or SQL Server to DuckDB, list every trigger first. Each one is a rule that silently stops running.