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 | +------------------------------+