List All PostgreSQL Triggers with Their State

calendar Jun 7, 2026 / · 7 min read · pg_trigger triggers pg_class schema inventory administration  ·
Share on: linkedin copy

List All PostgreSQL Triggers with Their State

A disabled trigger is invisible until something it should have done goes missing. The audit row that never got written, the derived column that stopped updating, the constraint check that silently stopped firing — each traces back to a trigger left in the disabled state after a bulk load or a restore. Listing files in \d does not show this, but pg_trigger.tgenabled does: one query returns every trigger across the database together with whether it actually fires.

Purpose and Overview

Triggers are stored in the pg_trigger system catalog. Each row records the trigger name, the table it belongs to, its definition, and a single character — tgenabled — that holds its firing state. That character is the answer to the question this query exists to answer: is this trigger live, or is it switched off?

A trigger can be disabled deliberately. Bulk-load tools often disable triggers to speed up large imports, and pg_restore can leave triggers disabled while it loads data. The risk is that nobody re-enables them. The trigger still exists, still shows up in the schema, but never runs.

pg_trigger also distinguishes user triggers from internal ones. Foreign-key enforcement is implemented with hidden triggers, and constraint indexes create their own. The tgisinternal column flags those, so a clean inventory filters them out and shows only the triggers a DBA actually wrote.

The value of querying the catalog directly, rather than reading per-table \d output one table at a time, is coverage. A single statement walks every schema and every table at once, which is what you need when the question is "is anything disabled anywhere?" rather than "what is on this one table?"

Sample Code

 1SELECT
 2    n.nspname AS schema_name,
 3    c.relname AS table_name,
 4    t.tgname  AS trigger_name,
 5    CASE t.tgenabled
 6        WHEN 'O' THEN 'enabled (origin/local)'
 7        WHEN 'D' THEN 'disabled'
 8        WHEN 'R' THEN 'enabled (replica)'
 9        WHEN 'A' THEN 'enabled (always)'
10    END       AS trigger_state,
11    pg_get_triggerdef(t.oid) AS definition
12FROM pg_trigger t
13JOIN pg_class     c ON c.oid = t.tgrelid
14JOIN pg_namespace n ON n.oid = c.relnamespace
15WHERE NOT t.tgisinternal
16ORDER BY n.nspname, c.relname, t.tgname;

Notes: Runs on PostgreSQL 9.0 and later (the tgisinternal column was added in 9.0). WHERE NOT t.tgisinternal removes the hidden triggers that enforce foreign keys and constraints. Drop the pg_get_triggerdef column if you only want the state summary.

Code Breakdown

The query joins three catalogs and translates one cryptic column into readable text.

The Joins

pg_trigger.tgrelid is the OID of the table the trigger sits on, so it joins to pg_class.oid to recover the table name. pg_class.relnamespace is the OID of the schema, so it joins to pg_namespace.oid to recover the schema name. These two joins turn raw object identifiers into the schema-qualified names a DBA reads.

The CASE on tgenabled

tgenabled stores a single character. The CASE expression maps each possible value — O, D, R, A — to a phrase that says what it means. Without this translation the result is a column of single letters that few people remember.

WHERE NOT tgisinternal

Internal triggers implement foreign keys, deferred constraints, and similar machinery. They are real pg_trigger rows but they are not something a DBA manages directly. Filtering on NOT tgisinternal keeps the inventory focused on application triggers.

pg_get_triggerdef

pg_get_triggerdef(t.oid) reconstructs the full CREATE TRIGGER statement from the catalog. It is the authoritative definition — the timing (BEFORE/AFTER), the events (INSERT/UPDATE/DELETE), the WHEN clause, and the function called — rendered as runnable DDL.

Key tgenabled States

'O' — Origin

The default. The trigger fires during normal operations on a session whose session_replication_role is the default value of origin. Almost every trigger you create is in this state.

'D' — Disabled

The trigger never fires, in any session, regardless of replication role. This is the state to hunt for. A disabled trigger is exactly the kind of silent gap that lets audit logging or derived-column maintenance stop without any error.

'R' — Replica

The trigger fires only when session_replication_role is set to replica. This is used by logical replication and replication tooling so that certain triggers run on the apply side but not during normal local writes.

'A' — Always

The trigger fires in both origin and replica roles. You set this with ALTER TABLE ... ENABLE ALWAYS TRIGGER when a trigger must run no matter how the change arrives, including during replication apply.

The R and A states only matter when something changes session_replication_role. In a server that never touches that setting, the practical distinction is simply enabled (O) versus disabled (D).

Practical Applications

A trigger inventory with state is a checklist you run at the moments triggers tend to get left switched off.

Post-Restore Audit

pg_restore can disable triggers while loading data. Run this query right after a restore and confirm that no application trigger is sitting in the D state. Anything disabled needs to be re-enabled or explicitly justified.

Finding Silently Skipped Logic

When a derived value or an audit trail stops updating with no error in the logs, a disabled trigger is a prime suspect. This query surfaces every D trigger in one place so you can check whether the missing behavior maps to one of them.

Verifying Replication-Role Configuration

In a logical replication setup, triggers that should run on apply must be A (always) or R (replica). The query lets you confirm that the triggers meant to fire during replication are in the correct state rather than plain origin.

Reviewing Definitions Before a Schema Change

pg_get_triggerdef gives you the exact DDL for every trigger. Before altering a table or dropping a column, you can scan the definitions to see which triggers reference what, and avoid breaking a trigger you forgot existed.

Migrating Triggers Between Environments

Because pg_get_triggerdef emits runnable CREATE TRIGGER statements, the query doubles as a way to extract trigger DDL from one database and replay it on another. Capturing the output before a rebuild gives you a known-good set of definitions to recreate, with their original timing and conditions intact.

Version Compatibility

The tgisinternal column was added in PostgreSQL 9.0, so the WHERE NOT t.tgisinternal filter requires 9.0 or later — effectively every supported version. pg_get_triggerdef has been available and stable across PostgreSQL 10 through 17.

The tgenabled state characters connect directly to SQL commands. ALTER TABLE ... DISABLE TRIGGER sets D, ENABLE TRIGGER restores O, ENABLE REPLICA TRIGGER sets R, and ENABLE ALWAYS TRIGGER sets A. The session_replication_role parameter, which the R and A states respond to, has behaved consistently across modern major versions, so this query and its interpretation carry forward unchanged.

Best Practices

  • Never leave triggers disabled after a bulk load — if a load process disables triggers for speed, track them and re-enable them as the final step, then confirm with this query.
  • Audit tgenabled after every pg_restore — restores can leave triggers off; make this query part of the post-restore checklist.
  • Use ENABLE ALWAYS for triggers that must fire under replication — a plain origin trigger will not run on the apply side of logical replication.
  • Document any trigger left in the disabled state — a D trigger should be a deliberate, recorded decision, not an accident waiting to be found.
  • Capture pg_get_triggerdef output before schema changes — keep the reconstructed DDL so you can review and recreate triggers safely.

References

Posts in this series