SQL / SECURITY, ROUTINES, AND DIALECTS
Triggers and the hidden work they do
Write per-row triggers in SQLite and PostgreSQL, and predict the extra writes, rewrites and failures a single statement will cause.
What you will learn
- Log OLD and NEW into an audit table with an AFTER UPDATE ... FOR EACH ROW trigger
- Add a WHEN guard so rows whose value did not change do not fire the trigger
- Multiply rows x triggers x chained triggers to get the real writes per statement
- Know the gaps: TRUNCATE and MySQL FK cascades never fire row-level triggers
Understanding Triggers and the hidden work they do
A trigger is a block of code registered against a table and a write event, invoked by the engine rather than by the caller. Inside it you get the pseudo-rows OLD and NEW, which hold the row as it was and as it will be, so you never have to re-read the table to see what changed. The important structural fact is that the trigger body runs inside the calling statement's transaction: its execution time is added to your statement's latency, its locks are your locks, and if it raises an error your statement fails and its changes roll back. A trigger is not a background job or a message queue.
FOR EACH ROW means exactly that. An UPDATE matching 500 rows fires the trigger 500 times, so a trigger that inserts one audit row turns 500 writes into 1000, and a trigger that also bumps a single summary row makes every concurrent writer queue behind that one row. Firing is driven by the event, not by the data: SQLite's AFTER UPDATE OF balance fires because balance appeared in the SET list, even when the new value equals the old one, which is why the WHEN clause is what actually filters. Triggers also chain, since a trigger's own INSERT can fire a trigger on the target table, and that second trigger's UPDATE can fire a third.
The real cost of triggers is that nothing at the call site mentions them. The driver's rows-affected count reports the statement's own rows and not the trigger's writes, so the number looks unchanged while the database wrote twice as much. A BEFORE trigger can assign to NEW and overwrite a value the client explicitly sent, so the row you read back is not the row you sent. And some write paths skip row-level triggers entirely, so an audit table everyone treats as complete quietly has holes. The useful mental model is that each trigger is an invisible clause bolted onto every write against that table: before you debug a slow or surprising statement, list the table's triggers.
CREATE TABLE account (
id INTEGER PRIMARY KEY,
balance INTEGER NOT NULL
);
CREATE TABLE balance_audit (
account_id INTEGER NOT NULL,
old_balance INTEGER NOT NULL,
new_balance INTEGER NOT NULL,
changed_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TRIGGER account_balance_audit
AFTER UPDATE OF balance ON account
FOR EACH ROW
WHEN OLD.balance <> NEW.balance -- without this, account 2 is logged too
BEGIN
INSERT INTO balance_audit (account_id, old_balance, new_balance)
VALUES (OLD.id, OLD.balance, NEW.balance);
END;
INSERT INTO account VALUES (1, 100), (2, 250), (3, 40);
UPDATE account SET balance = 250; -- one statement, three rows, three firings
SELECT 'rows at 250', count(*) FROM account WHERE balance = 250;
SELECT account_id, old_balance, new_balance FROM balance_audit ORDER BY account_id;A trigger is code the engine runs inside your transaction once per affected row, so one statement silently becomes many writes with their own cost, locks and failure modes.
Worked examples
One statement, five row changes
A trigger whose INSERT fires a second trigger, so a single UPDATE writes to three tables.
CREATE TABLE shipment (id INTEGER PRIMARY KEY, status TEXT NOT NULL);
CREATE TABLE shipment_event (shipment_id INTEGER, status TEXT);
CREATE TABLE event_count (n INTEGER NOT NULL);
INSERT INTO event_count VALUES (0);
CREATE TRIGGER shipment_status_event
AFTER UPDATE OF status ON shipment
FOR EACH ROW
BEGIN
INSERT INTO shipment_event VALUES (NEW.id, NEW.status);
END;
CREATE TRIGGER shipment_event_count
AFTER INSERT ON shipment_event
FOR EACH ROW
BEGIN
UPDATE event_count SET n = n + 1;
END;
INSERT INTO shipment VALUES (1, 'packed'), (2, 'packed');
UPDATE shipment SET status = 'shipped';
SELECT shipment_id, status FROM shipment_event ORDER BY shipment_id;
SELECT n FROM event_count;Example explained
Line 1The INSERT INTO shipment writes no events, because shipment_status_event is an AFTER UPDATE trigger only.
Line 2The single UPDATE matches two rows, so shipment_status_event runs twice and inserts two event rows.
Line 3Each of those inserts fires shipment_event_count, so event_count is updated twice and ends at 2.
Line 4Total row changes for one UPDATE statement: two updates, two inserts, two counter updates.
A BEFORE trigger overwriting what the client sent
In PostgreSQL a BEFORE row trigger can assign to NEW, so the stored row differs from the submitted one.
SET TIME ZONE 'UTC';
CREATE TABLE profile (
id int PRIMARY KEY,
nickname text NOT NULL,
updated_at timestamptz NOT NULL
);
CREATE FUNCTION stamp_profile() RETURNS trigger AS $$
BEGIN
-- a real trigger would use clock_timestamp(); fixed here so the output is stable
NEW.updated_at := '2026-09-03 20:00:00+00';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER profile_stamp
BEFORE UPDATE ON profile
FOR EACH ROW
EXECUTE FUNCTION stamp_profile();
INSERT INTO profile VALUES (1, 'ada', '2000-01-01 00:00:00+00');
UPDATE profile
SET nickname = 'ada.l',
updated_at = '1999-12-31 00:00:00+00'
WHERE id = 1;
SELECT nickname, updated_at FROM profile;Example explained
Line 1NEW.updated_at := ... discards the 1999 value the UPDATE supplied; the client is never told this happened.
Line 2RETURN NEW is what commits the modified row; returning NULL from a BEFORE row trigger would cancel the UPDATE for that row silently.
Line 3UPDATE 1 is reported even though the stored row does not match the statement, which is why round-tripping a value through the app can appear to lose the write.
Line 4In an AFTER trigger the same assignment would have no effect, because PostgreSQL ignores the return value of AFTER row triggers.
TRUNCATE walks past the trigger
Row-level DELETE triggers do not fire for TRUNCATE, so the audit table silently misses rows.
CREATE TABLE session_log (id int PRIMARY KEY);
CREATE TABLE deleted_session (id int);
CREATE FUNCTION log_deleted_session() RETURNS trigger AS $$
BEGIN
INSERT INTO deleted_session VALUES (OLD.id);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER session_log_del
AFTER DELETE ON session_log
FOR EACH ROW
EXECUTE FUNCTION log_deleted_session();
INSERT INTO session_log VALUES (1), (2), (3), (4);
DELETE FROM session_log WHERE id <= 2;
SELECT count(*) AS logged FROM deleted_session;
TRUNCATE session_log;
SELECT count(*) AS logged FROM deleted_session;Example explained
Line 1DELETE 2 fires the trigger once per removed row, so deleted_session holds ids 1 and 2.
Line 2TRUNCATE removes rows 3 and 4 by discarding storage rather than deleting rows, so no row-level trigger runs.
Line 3The second count is still 2: the audit table now claims two sessions were removed when four were.
Line 4Covering this gap needs a separate AFTER TRUNCATE ... FOR EACH STATEMENT trigger, not the row trigger.
Important notes
Timing rules differ by engine: MySQL AFTER triggers cannot modify NEW, PostgreSQL ignores the return value of AFTER row triggers, and SQLite has no statement-level triggers and no assignment to NEW at all, so validation there is done with SELECT RAISE(ABORT, 'message').
CURRENT_TIMESTAMP and now() inside a PostgreSQL trigger return the transaction start time, so every row touched by one long transaction gets an identical stamp; use clock_timestamp() when you need the moment the row was written.
Common mistakes
Re-querying the table inside the trigger instead of using OLD and NEW, for example SELECT balance FROM account WHERE id = NEW.id in an AFTER UPDATE trigger; that returns the new value, so the audit row records the same number in both the old and new column and the log looks like nothing ever changed.
Assuming the trigger runs once per statement rather than once per row; a nightly 50,000-row UPDATE then performs 50,000 audit inserts plus 50,000 updates of one counter row, which serialises every concurrent writer on that single row and turns a fast statement into a lock queue.
Treating a trigger error as a per-row skip; a RAISE(ABORT) in SQLite or an exception in a PL/pgSQL trigger aborts the whole statement, so a 1000-row INSERT that trips the trigger on row 900 inserts nothing at all.
Try it yourself
Change, predict, then run
In a SQLite editor create stock(id, qty) and stock_alert(id, old_qty, new_qty), then write an AFTER UPDATE OF qty trigger with a WHEN clause that logs an alert only when qty crosses from 5 or more down to below 5. Insert four rows with qty 10, 6, 4 and 3, run one UPDATE that sets qty = qty - 3 for all of them, and check how many alert rows you get and why it is not four.
Open the SQL workspaceCheck your understanding
An AFTER UPDATE ... FOR EACH ROW trigger on orders inserts one row into order_audit. Your app runs a single UPDATE that matches 500 rows, and the driver reports 500 rows affected. What actually happened?
- 500 rows were written, because the trigger's insert replaced the update on each row
- 501 rows were written, because the trigger ran once for the whole statement
- 1000 rows were written, and the reported 500 counts only the orders table
- 1000 rows were written, and the driver reports 1000 because trigger writes are counted
Show answer
A row trigger fires once per affected row, so there are 500 updates in orders plus 500 inserts in order_audit, and the rows-affected value the driver returns describes the statement's own target table, not writes made by triggers. Option 1 is tempting because statement-level triggers do exist in PostgreSQL and SQL Server, but FOR EACH ROW explicitly asks for per-row firing; a statement-level trigger also could not use NEW to know which row to audit.