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.

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
-- 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();-- 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();-- 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 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
| Property | BEFORE | AFTER | INSTEAD OF |
|---|---|---|---|
| Row already written | No | Yes | Not at all |
Can change NEW | Yes | No | Yes, before doing the real work |
| Can abort the statement | RAISE / SIGNAL | RAISE / SIGNAL (rolls back) | Same |
| Sees generated columns | Result not yet computed | Computed values visible | n/a |
| Works on views | No | No | Yes |
Fires on ON CONFLICT DO NOTHING | No | No | Not applicable |
Fires on TRUNCATE | No | Only with a TRUNCATE trigger | No |
| Return value matters | NEW / OLD / NULL | Ignored | NEW marks the row processed |
| Engine | Statement-level | Transition tables | Recursion guard | Disable a trigger |
|---|---|---|---|---|
| PostgreSQL | FOR EACH STATEMENT | REFERENCING on AFTER | pg_trigger_depth() | ALTER TABLE ... DISABLE TRIGGER |
| MySQL | No | No | max_sp_recursion_depth, error 1442 | Not supported per trigger |
| SQL Server | Always statement-level | inserted / deleted | RECURSIVE_TRIGGERS setting | DISABLE TRIGGER, sp_settriggerorder |
| Oracle | FOR EACH STATEMENT | REFERENCING NEW TABLE | :NEW mutation restriction | ALTER 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.
INSERT INTO orders (id, total_cents) VALUES (1, 500);
-- INSERT 0 1 ... but SELECT count(*) FROM orders WHERE id = 1; -> 0There 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
-- 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 exceededMySQL 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:
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.
-- 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.
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.
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.
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 symptom | Cause | Fix |
|---|---|---|
INSERT 0 1 but no row stored | BEFORE row trigger returned NULL | Return NEW |
stack depth limit exceeded | Trigger recurses into its own table | Add pg_trigger_depth() guard or a WHEN condition |
| MySQL error 1442 | Trigger modifies its own table | Move the logic to a procedure called after the statement |
| Audit table has no delete rows | TRUNCATE bypasses row triggers | Add a BEFORE TRUNCATE trigger or audit in the app |
| Trigger never fires on the replica | Logical apply runs with session_replication_role = replica | Create the trigger with ENABLE REPLICA / ENABLE ALWAYS |
cannot insert into view despite a trigger | Trigger is BEFORE/AFTER; views need INSTEAD OF | Recreate as INSTEAD OF |
RETURNING returns nothing from a view insert | INSTEAD OF function returned NULL | RETURN NEW after filling the key |
| Trigger order unexpected | PostgreSQL fires alphabetically by trigger name | Name triggers with a numeric prefix, or merge into one |
| Update to one row takes seconds | Trigger updates a shared counter or runs a heavy query | Move to an AFTER statement trigger or a queue |
SIGNAL / RAISE message invisible to the client | Generic SQLSTATE mapped to a 500 | Use 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.