What this usually means
Most often, the trigger is defined correctly but is not firing because of a mismatch between the trigger event and the actual operation. Common causes: the trigger is defined as BEFORE but you expect AFTER (or vice versa), the trigger is statement-level but you need row-level (or vice versa), or the trigger function returns NULL in a ROW trigger (which cancels the operation silently). Other frequent causes include the trigger being disabled, the table being a foreign table or partition that doesn't inherit triggers, or the trigger function raising an exception that is caught somewhere. Postgres does not log trigger execution by default, so you must actively instrument.
The first ten minutes — establish facts before touching code.
- 1SELECT tgname, tgtype::int::bit(8), tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid = 'your_table'::regclass;
- 2SELECT proname, prosrc FROM pg_proc WHERE proname = 'your_trigger_function';
- 3SET client_min_messages = DEBUG1; then run the DML and watch for 'trigger fired' messages
- 4Check if trigger is disabled: ALTER TABLE your_table ENABLE TRIGGER ALL;
The specific files, logs, configs, and dashboards that usually own this bug.
- searchpg_trigger: check tgenabled (O=disabled, D=session_replica_role, A=always, E=enabled)
- searchpg_proc: verify trigger function exists and returns TRIGGER
- searchPostgres logs (pg_log directory): look for ERROR or WARNING at time of DML
- searchpg_stat_activity: check if any active transaction holds locks that might defer trigger
- searchInformation schema: SELECT * FROM information_schema.triggers WHERE event_object_table = 'your_table';
Practical causes, not theory. These are the things you will actually find.
- warningTrigger is defined as BEFORE but you expect AFTER behavior (or vice versa)
- warningTrigger function returns NULL in a ROW-level trigger, causing the row operation to be skipped
- warningTrigger is disabled (tgenabled = 'O') or only fires on replica (session_replica_role)
- warningTrigger is statement-level but you need row-level; the DML affects zero rows
- warningTrigger function raises an exception that is caught by application code
- warningTable is a partition and triggers are not inherited; you need to define trigger on each partition
Concrete fix directions. Pick the one that matches your root cause.
- buildChange trigger timing from BEFORE to AFTER or vice versa
- buildEnsure trigger function returns NEW for INSERT/UPDATE, OLD for DELETE, or NULL only if you intend to skip
- buildRe-enable trigger: ALTER TABLE table_name ENABLE TRIGGER trigger_name;
- buildIf using partitioning, create triggers on each partition or use a trigger on the parent with tg_when = 'BEFORE' and condition to avoid duplicates
- buildAdd RAISE NOTICE in the trigger function to confirm it's called
A fix you cannot prove is a guess. Close the loop.
- verifiedAdd RAISE NOTICE 'Trigger fired for %', TG_OP; at the start of the trigger function and check logs
- verifiedRun a simple UPDATE that affects one row and immediately query pg_stat_user_tables to see n_tup_upd; if it increments, the table is being written
- verifiedUse pg_trigger_depth() inside the function to log nesting level
- verifiedTemporarily set log_statement = 'mod' and log_min_duration_statement = 0 to capture all DML
- verifiedWrite a minimal test case in a psql session: BEGIN; INSERT ...; SELECT * FROM audit_table; ROLLBACK;
Things that make this bug worse or harder to find.
- warningNot checking tgenabled after a pg_dump/restore (triggers are restored as enabled, but if you used --disable-triggers, they remain disabled)
- warningAssuming triggers work on foreign tables (they don't; use event triggers or rules)
- warningOverlooking that a BEFORE DELETE RETURNING NULL prevents the delete; that's by design
- warningForgetting that statement-level triggers fire only once per statement, even if zero rows are affected
- warningRelying on trigger side effects without adding logging; always instrument
Audit Trigger Silently Skipped After Schema Migration
Timeline
- 09:15Deploy schema migration that adds a column to orders table
- 09:30Customer reports missing audit entries for new orders
- 09:45Check audit_logs table: last entry from 09:14, before deploy
- 10:00Run INSERT manually via psql; no audit entry created
- 10:10Check pg_trigger: trigger is 'DISABLED' (tgenabled='O')
- 10:12Check migration file: uses disable_trigger! in Rails which sets triggers disabled
- 10:15Re-enable trigger: ALTER TABLE orders ENABLE TRIGGER audit_trigger;
- 10:16Test INSERT; audit entry appears. Issue resolved.
We had a production incident where orders were being created but the audit trail stopped updating. The audit system was critical for compliance, and we didn't notice until a customer complained about missing history. At first, I suspected a bug in the trigger function itself—maybe a syntax error from the migration? But the migration only added a column, no changes to the trigger function.
I checked pg_trigger and saw tgenabled = 'O'. That's 'disabled' in Postgres. How did that happen? I dug into the Rails migration: we used a gem that calls disable_trigger! to avoid trigger interference during bulk data loading. The migration did run disable_trigger! but never re-enabled it because the developer assumed the gem would handle it. It didn't.
The fix was simple: ALTER TABLE orders ENABLE TRIGGER audit_trigger; But the lesson was painful: never trust a migration helper that toggles triggers without explicit enable at the end. Now we always check pg_trigger after any migration that touches triggers, and we wrap disable/enable in a transaction with proper error handling.
Root cause
Rails migration used disable_trigger! which set the trigger to disabled state, and migration did not re-enable it.
The fix
Re-enabled the trigger with ALTER TABLE orders ENABLE TRIGGER audit_trigger;
The lesson
Always verify trigger state after any schema change that touches triggers, and never assume third-party gems restore them.
When a DML statement executes, Postgres checks for triggers in the order: BEFORE statement triggers, BEFORE row triggers (for each affected row), AFTER row triggers, AFTER statement triggers. If any BEFORE ROW trigger returns NULL, the row operation is skipped (no INSERT/UPDATE/DELETE occurs for that row). This is a common hidden cause of 'trigger not firing'—the trigger fires, but its return value cancels the operation.
To see if a trigger actually fired, add a RAISE NOTICE at the beginning of the trigger function. Postgres does not log trigger invocations by default. You can also set client_min_messages = DEBUG1 to see 'process_utility' messages, but trigger-specific debug output requires plpgsql debug or custom logging.
Partitioned tables in Postgres 10+ do not inherit triggers defined on the parent. Each partition must have its own trigger definition. If you define a trigger on the parent, it only fires when you directly insert/update/delete the parent (which is not how partitioned tables work). You need to either create the trigger on each partition (using a loop in a function) or use an event trigger to automatically attach triggers to new partitions.
Foreign tables also do not support triggers. If you have a foreign table and try to create a trigger, Postgres will throw an error. For audit logging on foreign tables, consider using a wrapper view with rules or an application-level hook.
A row-level trigger function must return a value of type TRIGGER. For INSERT and UPDATE, return NEW; for DELETE, return OLD. If you return NULL, the row operation is skipped. If you return something else (e.g., integer), Postgres raises an error. This is a common mistake when copying trigger functions from other databases or when modifying the function body.
Statement-level triggers should not return anything (the function's return type is void, but you can still return NULL). In practice, always ensure your trigger function explicitly returns NEW or OLD for row-level triggers, and avoid NULL unless you intend to suppress the operation.
The pg_trigger catalog holds all triggers. Key columns: tgenabled (O=disabled, D=trigger fires on replica, A=always, E=enabled), tgtype (bitmask encoding timing and event), tgrelid (table OID), tgfoid (function OID). To quickly check if a trigger is enabled: SELECT tgenabled FROM pg_trigger WHERE tgname = 'my_trigger';. If it's 'O', the trigger is disabled.
You can also use information_schema.triggers, but it lacks the tgenabled column. Always prefer pg_trigger for debugging. If you need to re-enable all triggers on a table: ALTER TABLE table_name ENABLE TRIGGER ALL;.
Frequently asked questions
Why does my trigger work in psql but not from my application?
Most likely the application uses a different database user or connection that has triggers disabled at session level (e.g., SET session_replication_role = replica). Check the application's database connection settings and ensure it's not disabling triggers. Also verify the trigger function is owned by a superuser or has proper permissions.
Can a BEFORE DELETE trigger prevent the delete from happening?
Yes. If a BEFORE DELETE ROW trigger function returns NULL, the delete is skipped for that row. This is by design and is used for conditional deletes. Make sure your function returns OLD for DELETE unless you intend to cancel it.
Do triggers fire on TRUNCATE?
By default, no. TRUNCATE does not fire DML triggers. However, you can create a trigger on TRUNCATE event (BEFORE TRUNCATE or AFTER TRUNCATE) using CREATE TRIGGER ... ON table_name FOR EACH STATEMENT EXECUTE FUNCTION ...;. There is no row-level TRUNCATE trigger.
How do I log trigger executions without modifying the trigger function?
You can set log_statement = 'mod' in postgresql.conf (or ALTER SYSTEM) to log all DML statements, but that doesn't show trigger invocation. To see trigger calls, you must add RAISE NOTICE or use auto_explain with log_trigger_stats (Postgres 15+). Alternatively, use pg_stat_user_functions to see function execution counts.
Why does my trigger function run fine when called directly but not as a trigger?
Trigger functions have special variables like NEW, OLD, TG_OP, TG_TABLE_NAME. If you call the function directly, these variables are NULL or undefined, so the function may behave differently. Always test trigger functions by executing a DML on the table, not by SELECT my_function();.