Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL Triggers: BEFORE, AFTER, INSTEAD OF Syntax and Common Pitfalls

A BEFORE trigger that returns NULL deletes the row without an error, bulk updates fire it once per row, and TRUNCATE skips it entirely.

~5 min readSQL
#SQL#Triggers#Automation
3D illustration of automated event tripwires firing BEFORE and AFTER insert and update into an audit logging pipeline

Quick Summary

A trigger is a function the database runs for you when a specified event happens on a table or view. The event is INSERT, UPDATE, DELETE, or TRUNCATE; the timing is BEFORE (modify or reject the row), AFTER (react to a change that already happened), or INSTEAD OF (replace the operation entirely, which is how views become writable). The granularity is FOR EACH ROW or, in PostgreSQL and Oracle, once per statement.

Triggers are the right tool for three jobs: auditing changes with the old and new values intact, maintaining derived data that cannot be expressed as a generated column, and enforcing invariants that check constraints cannot express — cross-row or deferred. Everything else is a liability, because a trigger is logic that fires without appearing in the calling code, cannot be skipped by a well-meaning service, and runs inside the same transaction and lock footprint as the statement that woke it.

The pitfalls are consistent across engines: a BEFORE trigger that returns null and silently removes rows, an audit trigger that misses TRUNCATE, a bulk update that fires a hundred thousand times, and a trigger that writes to the same table and hits the engine's recursion guard.

Basic syntax and query signatures

sql
-- PostgreSQL: trigger function first, then the trigger CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; -- NULL would skip the row entirely END $$; CREATE TRIGGER orders_set_updated_at BEFORE UPDATE ON orders FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION set_updated_at(); -- unconditional on insert, restricted to one column on update CREATE TRIGGER orders_audit_ins AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION audit_row(); CREATE TRIGGER orders_touch_plan BEFORE UPDATE OF plan_id ON orders FOR EACH ROW EXECUTE FUNCTION bump_plan_version();
sql
-- PostgreSQL: statement-level trigger with transition tables, one audit row per statement CREATE OR REPLACE FUNCTION audit_bulk() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO audit_log (table_name, op, row_count, at) SELECT TG_TABLE_NAME, TG_OP, count(*), now() FROM new_rows; RETURN NULL; END $$; CREATE TRIGGER orders_audit_bulk AFTER UPDATE ON orders REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows FOR EACH STATEMENT EXECUTE FUNCTION audit_bulk();
sql
-- MySQL: no statement-level triggers, NEW/OLD only, one event per trigger DELIMITER $$ CREATE TRIGGER orders_before_update BEFORE UPDATE ON orders FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); IF NEW.total_cents < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'total_cents must be >= 0'; END IF; END $$ DELIMITER ;
sql
-- SQL Server: statement-level by nature, with inserted/deleted pseudo tables CREATE TRIGGER trg_orders_audit ON dbo.Orders AFTER UPDATE, DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.AuditLog (op, order_id, changed_at) SELECT CASE WHEN d.id IS NULL THEN 'insert' WHEN i.id IS NULL THEN 'delete' ELSE 'update' END, COALESCE(i.id, d.id), SYSUTCDATETIME() FROM inserted i FULL OUTER JOIN deleted d ON d.id = i.id; END;

Key comparison table

PropertyBEFOREAFTERINSTEAD OF
Row already writtenNoYesNot at all
Can change NEWYesNoYes, before doing the real work
Can abort the statementRAISE / SIGNALRAISE / SIGNAL (rolls back)Same
Sees generated columnsResult not yet computedComputed values visiblen/a
Works on viewsNoNoYes
Fires on ON CONFLICT DO NOTHINGNoNoNot applicable
Fires on TRUNCATENoOnly with a TRUNCATE triggerNo
Return value mattersNEW / OLD / NULLIgnoredNEW marks the row processed
EngineStatement-levelTransition tablesRecursion guardDisable a trigger
PostgreSQLFOR EACH STATEMENTREFERENCING on AFTERpg_trigger_depth()ALTER TABLE ... DISABLE TRIGGER
MySQLNoNomax_sp_recursion_depth, error 1442Not supported per trigger
SQL ServerAlways statement-levelinserted / deletedRECURSIVE_TRIGGERS settingDISABLE TRIGGER, sp_settriggerorder
OracleFOR EACH STATEMENTREFERENCING NEW TABLE:NEW mutation restrictionALTER TRIGGER ... DISABLE

Practical edge cases and deep dives

1. RETURN NULL in a BEFORE trigger deletes the row

In PostgreSQL a row-level BEFORE trigger decides whether the row proceeds: return NEW to let it through, NULL to skip it. A trigger function written for auditing and copied into a BEFORE position will return NULL on every row, and the INSERT reports success while storing nothing.

sql
INSERT INTO orders (id, total_cents) VALUES (1, 500); -- INSERT 0 1 ... but SELECT count(*) FROM orders WHERE id = 1; -> 0

There is no error to catch. The only reliable defence is to keep BEFORE triggers to one purpose — mutate NEW and return it — and to write audit triggers as AFTER, where the return value is ignored.

2. A trigger that writes to the table that fired it

code
-- MySQL ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger -- PostgreSQL: allowed, but recursion is your problem ERROR: stack depth limit exceeded

MySQL refuses the self-reference outright. PostgreSQL allows it and lets you recurse until the stack dies, which is why every recursive trigger needs an exit condition — WHEN (OLD.status IS DISTINCT FROM NEW.status) — or a depth check:

sql
IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF;

The same guard protects a chain of triggers that call each other across tables. In SQL Server the equivalent is sp_configure 'nested triggers', which caps the chain at 32 by default and raises "Maximum nesting level of 32 was exceeded".

3. Bulk statements fire per row, and that is where performance dies

A single UPDATE orders SET plan_id = 9 over 200,000 rows runs a row trigger 200,000 times. If each invocation inserts an audit row, the transaction now holds 200,000 extra inserts, a much larger WAL volume, and — if the trigger increments a shared counter — a serialised hot row that turns a fast update into a lock queue.

sql
-- audit per statement instead of per row CREATE TRIGGER orders_audit_bulk AFTER UPDATE ON orders REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows FOR EACH STATEMENT EXECUTE FUNCTION audit_bulk();

MySQL has no statement-level triggers, so the equivalent is to audit in the application for bulk paths and keep the trigger for single-row writes, or to insert into a staging table from the trigger and summarise afterwards. Measure before assuming: EXPLAIN ANALYZE on a statement with a row trigger shows the trigger time inside the node's actual time only in some engines; timing the statement with and without the trigger is the reliable test.

4. Audit triggers miss deletes that bypass them

TRUNCATE does not fire INSERT, UPDATE, or DELETE row triggers. Neither does ON CONFLICT DO NOTHING (the row is not written, so nothing fires) nor a BEFORE trigger that returns NULL. On a logical replica, triggers do not run at all unless they are marked as replica triggers, because the apply process sets session_replication_role = replica.

The practical consequence: an audit table fed only by triggers has gaps exactly where the destructive operations are. Cover TRUNCATE explicitly, and reconcile the audit table against a checksum or a row count on a schedule.

sql
CREATE TRIGGER orders_no_truncate BEFORE TRUNCATE ON orders FOR EACH STATEMENT EXECUTE FUNCTION block_truncate();

5. INSTEAD OF triggers and RETURNING

A view over a join is not updatable, and INSERT INTO v ... normally fails with cannot insert into view. An INSTEAD OF row trigger on the view receives the attempted row and does the real work against the base tables. The return value matters: returning NEW tells the engine the row was handled, which is what makes RETURNING and INSERT ... RETURNING id work; returning NULL reports zero rows affected.

sql
CREATE OR REPLACE FUNCTION insert_order_item() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO order_items (order_id, product_id, quantity) VALUES (NEW.order_id, NEW.product_id, NEW.quantity) RETURNING id INTO NEW.id; RETURN NEW; END $$;

6. Deferrable constraint triggers for cross-row invariants

A check constraint sees one row. When the invariant is "the sum of allocations must equal the invoice total", you need something that runs after the statement, or at commit. PostgreSQL's CREATE CONSTRAINT TRIGGER ... DEFERRABLE INITIALLY DEFERRED is the tool, and it is the only way to check an invariant that is briefly violated in the middle of a multi-statement transaction.

sql
CREATE CONSTRAINT TRIGGER allocations_balanced AFTER INSERT OR UPDATE OR DELETE ON allocations DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION assert_allocations_balanced();

Oracle has the same construct (CONSTRAINT ... DEFERRABLE triggers with FOLLOWS ordering), and SQL Server accomplishes it with a deferred check inside the transaction. Use it sparingly: a deferred check that fails rolls back the whole transaction at COMMIT, far from the statement that caused it, and the error message is rarely precise about which row.

Common mistakes and runtime errors

Error or symptomCauseFix
INSERT 0 1 but no row storedBEFORE row trigger returned NULLReturn NEW
stack depth limit exceededTrigger recurses into its own tableAdd pg_trigger_depth() guard or a WHEN condition
MySQL error 1442Trigger modifies its own tableMove the logic to a procedure called after the statement
Audit table has no delete rowsTRUNCATE bypasses row triggersAdd a BEFORE TRUNCATE trigger or audit in the app
Trigger never fires on the replicaLogical apply runs with session_replication_role = replicaCreate the trigger with ENABLE REPLICA / ENABLE ALWAYS
cannot insert into view despite a triggerTrigger is BEFORE/AFTER; views need INSTEAD OFRecreate as INSTEAD OF
RETURNING returns nothing from a view insertINSTEAD OF function returned NULLRETURN NEW after filling the key
Trigger order unexpectedPostgreSQL fires alphabetically by trigger nameName triggers with a numeric prefix, or merge into one
Update to one row takes secondsTrigger updates a shared counter or runs a heavy queryMove to an AFTER statement trigger or a queue
SIGNAL / RAISE message invisible to the clientGeneric SQLSTATE mapped to a 500Use a documented SQLSTATE and surface the message

A closing rule of thumb: if the business needs to know that something happened, the event belongs in the application with an outbox table; if the database needs to stay consistent no matter who writes, the trigger belongs in the database. Triggers that exist because "someone might forget to call the service" are correct. Triggers that duplicate what the service already does are double work that will drift.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.