2 Ways to List all Triggers in a PostgreSQL Database

Here are two options for listing out the triggers in a PostgreSQL database.

The information_schema.triggers View

This view contains all functions and procedures in the current database that the current user owns or has some privilege other than SELECT on.

Here’s an example of returning a list of triggers:

SELECT
    trigger_schema,
    trigger_name,
    event_object_table
FROM 
    information_schema.triggers
ORDER BY 
    event_object_table;

Example result:

+----------------+-----------------------+--------------------+
| trigger_schema |     trigger_name      | event_object_table |
+----------------+-----------------------+--------------------+
| public         | last_updated          | actor              |
| public         | last_updated          | address            |
| public         | last_updated          | category           |
| public         | last_updated          | city               |
| public         | last_updated          | country            |
| public         | last_updated          | customer           |
| public         | film_fulltext_trigger | film               |
| public         | film_fulltext_trigger | film               |
| public         | last_updated          | film               |
| public         | last_updated          | film_actor         |
| public         | last_updated          | film_category      |
| public         | last_updated          | inventory          |
| public         | last_updated          | language           |
| public         | last_updated          | rental             |
| public         | last_updated          | staff              |
| public         | last_updated          | store              |
+----------------+-----------------------+--------------------+

Feel free to include more columns as required. For example, you can include the action_statement column to include the trigger’s definition.

The pg_trigger Catalog

The pg_catalog.pg_trigger catalog stores triggers on tables and views.

Here’s an example of code that returns a list of triggers and their table:

SELECT 
    tgname AS trigger_name,
    tgrelid::regclass AS table_name
FROM 
    pg_trigger
ORDER BY 
    table_name,
    trigger_name;

That can return quite a lot of triggers, depending on the database.

We can narrow it down to only those triggers for a given table like this:

SELECT 
    tgname AS trigger_name
FROM 
    pg_trigger
WHERE
    tgrelid = 'public.film'::regclass
ORDER BY
    trigger_name;

Example result:

+------------------------------+
|         trigger_name         |
+------------------------------+
| RI_ConstraintTrigger_a_24890 |
| RI_ConstraintTrigger_a_24891 |
| RI_ConstraintTrigger_a_24900 |
| RI_ConstraintTrigger_a_24901 |
| RI_ConstraintTrigger_a_24915 |
| RI_ConstraintTrigger_a_24916 |
| RI_ConstraintTrigger_c_24907 |
| RI_ConstraintTrigger_c_24908 |
| RI_ConstraintTrigger_c_24912 |
| RI_ConstraintTrigger_c_24913 |
| film_fulltext_trigger        |
| last_updated                 |
+------------------------------+