Here are three options for listing out the triggers for a given table in PostgreSQL.
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.
One of the columns in this view is event_object_table, which contains the name of the table that the trigger is defined on.
We can therefore use this view to get the triggers for a specific table like this:
SELECT
trigger_schema,
trigger_name,
event_manipulation,
action_statement
FROM information_schema.triggers
WHERE event_object_table = 'film';Example result:
+----------------+-----------------------+--------------------+----------------------------------------------------------------------------------------------------+
| trigger_schema | trigger_name | event_manipulation | action_statement |
+----------------+-----------------------+--------------------+----------------------------------------------------------------------------------------------------+
| public | film_fulltext_trigger | INSERT | EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') |
| public | film_fulltext_trigger | UPDATE | EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') |
| public | last_updated | UPDATE | EXECUTE FUNCTION last_updated() |
+----------------+-----------------------+--------------------+----------------------------------------------------------------------------------------------------+
Seeing as the view returns only those triggers that the current user owns or has some privilege other than SELECT on, the resulting list could represent only a subset of the actual triggers that are defined on the table.
The pg_trigger Catalog
The pg_catalog.pg_trigger catalog stores triggers on tables and views, and it is not limited to just those triggers that the current user owns or has some privilege other than SELECT on:
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 | +------------------------------+
To get the source, we can include the pg_proc view in our query:
SELECT
t.tgname,
p.prosrc
FROM
pg_trigger t JOIN pg_proc p ON p.oid = t.tgfoid
WHERE
t.tgrelid = 'public.film'::regclass
ORDER BY
t.tgname;Example result:
+------------------------------+------------------------------------------+ | tgname | prosrc | +------------------------------+------------------------------------------+ | RI_ConstraintTrigger_a_24890 | RI_FKey_restrict_del | | RI_ConstraintTrigger_a_24891 | RI_FKey_cascade_upd | | RI_ConstraintTrigger_a_24900 | RI_FKey_restrict_del | | RI_ConstraintTrigger_a_24901 | RI_FKey_cascade_upd | | RI_ConstraintTrigger_a_24915 | RI_FKey_restrict_del | | RI_ConstraintTrigger_a_24916 | RI_FKey_cascade_upd | | RI_ConstraintTrigger_c_24907 | RI_FKey_check_ins | | RI_ConstraintTrigger_c_24908 | RI_FKey_check_upd | | RI_ConstraintTrigger_c_24912 | RI_FKey_check_ins | | RI_ConstraintTrigger_c_24913 | RI_FKey_check_upd | | film_fulltext_trigger | tsvector_update_trigger_byid | | last_updated | +| | | BEGIN +| | | NEW.last_update = CURRENT_TIMESTAMP;+| | | RETURN NEW; +| | | END | +------------------------------+------------------------------------------+
Bear in mind that the contents of the pg_proc.prosrc column depends on the implementation language/call convention. It could contain the actual source code of the function for interpreted languages, a link symbol, a file name, or just about anything else.
The \dS Command
When using psql, the the \dS command is a quick way to get information about tables, views, etc, including any triggers that are defined against them.
We can append the table name to this command to return information about just that table:
\dS filmExample result:
+----------------------+-----------------------------+-----------+----------+---------------------------------------+
| Column | Type | Collation | Nullable | Default |
+----------------------+-----------------------------+-----------+----------+---------------------------------------+
| film_id | integer | | not null | nextval('film_film_id_seq'::regclass) |
| title | character varying(255) | | not null | |
| description | text | | | |
| release_year | year | | | |
| language_id | smallint | | not null | |
| original_language_id | smallint | | | |
| rental_duration | smallint | | not null | 3 |
| rental_rate | numeric(4,2) | | not null | 4.99 |
| length | smallint | | | |
| replacement_cost | numeric(5,2) | | not null | 19.99 |
| rating | mpaa_rating | | | 'G'::mpaa_rating |
| last_update | timestamp without time zone | | not null | now() |
| special_features | text[] | | | |
| fulltext | tsvector | | not null | |
+----------------------+-----------------------------+-----------+----------+---------------------------------------+
Indexes:
"film_pkey" PRIMARY KEY, btree (film_id)
"film_fulltext_idx" gist (fulltext)
"idx_fk_language_id" btree (language_id)
"idx_fk_original_language_id" btree (original_language_id)
"idx_title" btree (title)
Foreign-key constraints:
"film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
"film_original_language_id_fkey" FOREIGN KEY (original_language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
TABLE "film_actor" CONSTRAINT "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "film_category" CONSTRAINT "film_category_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "inventory" CONSTRAINT "inventory_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
film_fulltext_trigger BEFORE INSERT OR UPDATE ON film FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE FUNCTION last_updated()