Summary: in this tutorial, you will learn how to list all triggers in the current database or triggers associated with a specific table in PostgreSQL.
Neon Postgres - You build apps, we'll run the DB. Neon is serverless Postgres with time-saving features like autoscaling. Start Free
Sponsored
Listing all triggers using SQL statement
To list all triggers along with their associated tables in the current database, you can use the information_schema.triggers
system view.
For example, the following statement retrieves the triggers with their associated tables in the current database from the information_schema.triggers
view:
SELECT
event_object_table AS table_name,
trigger_name
FROM
information_schema.triggers
GROUP BY
table_name,
trigger_name
ORDER BY
table_name,
trigger_name;
Code language: SQL (Structured Query Language) (sql)
Sample output:
table_name | trigger_name
------------+-------------------------------
employees | after_delete_employee_trigger
members | after_insert_member_trigger
products | before_delete_product_trigger
salaries | after_update_salary_trigger
(4 rows)
Code language: SQL (Structured Query Language) (sql)
If you want to list all triggers associated with a specific table, you can filter the event_object_table
column by specifying the table name in the WHERE
clause.
For example, the following query lists all triggers associated with the employees
table in the current database:
SELECT
event_object_table AS table_name,
trigger_name
FROM
information_schema.triggers
WHERE
event_object_table = 'employees'
GROUP BY
table_name,
trigger_name
ORDER BY
table_name,
trigger_name;
Code language: SQL (Structured Query Language) (sql)
Output:
table_name | trigger_name
------------+-------------------------------
employees | after_delete_employee_trigger
(1 row)
Code language: SQL (Structured Query Language) (sql)
To make it more convenient, you can create a user-defined function that wraps the above query.
For example, the following creates a function named get_triggers()
that returns all triggers with their associated tables in the current database:
create or replace function get_triggers()
returns table (
table_name text,
trigger_name text
)
language plpgsql
as
$$
begin
return query select
trg.event_object_table::text AS table_name,
trg.trigger_name::text
from
information_schema.triggers trg
group by
table_name,
trg.trigger_name
order by
table_name,
trg.trigger_name;
end;
$$;
Code language: SQL (Structured Query Language) (sql)
The following statement shows how to call the get_triggers()
function:
SELECT * FROM get_triggers();
Code language: SQL (Structured Query Language) (sql)
The following creates a function get_triggers()
that accepts a table name and returns all the triggers of the table:
create or replace function get_triggers(
p_table_name text
)
returns table (
table_name text,
trigger_name text
)
language plpgsql
as
$$
begin
return query select
event_object_table::text AS table_name,
trg.trigger_name::text
from
information_schema.triggers trg
where
event_object_table = p_table_name
group by
table_name,
trg.trigger_name
order by
table_name,
trg.trigger_name;
end;
$$;
Code language: SQL (Structured Query Language) (sql)
The following statement uses the get_triggers(text)
function to retrieve all triggers of the employees
table:
SELECT * FROM get_triggers('employees');
Code language: SQL (Structured Query Language) (sql)
Listing all triggers using the pg_trigger view
pg_trigger
is a system view that provides information about triggers defined in the database. Here are some important columns:
Column | Description |
---|---|
tgname | The name of the trigger. |
tgrelid | The object ID of the table or view to which the trigger belongs. |
tgfoid | The object ID of the function is called when the trigger fires. |
tgtype | The type of the trigger, such as BEFORE , AFTER , or INSTEAD OF . |
tgenabled | The status of the trigger, either enabled or disabled. |
tgisinternal | The boolean indicator indicates whether the trigger is a system-generated trigger or a user-defined trigger. |
For example, the following statement retrieves all user-defined triggers of the employees
table from the pg_trigger
view:
SELECT
tgname AS trigger_name
FROM
pg_trigger
WHERE
tgrelid = 'employees' :: regclass
AND tgisinternal = false
ORDER BY
trigger_name;
Code language: SQL (Structured Query Language) (sql)
Output:
trigger_name
-------------------------------
after_delete_employee_trigger
(1 row)
Code language: SQL (Structured Query Language) (sql)
Listing all triggers using psql
First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL server using psql:
psql -U postgres
Code language: SQL (Structured Query Language) (sql)
Second, use the \dS
command with a table name to list all the triggers associated with the table:
\dS table_name;
Code language: SQL (Structured Query Language) (sql)
For example, the following command displays the employees
table with its triggers:
\dS employees
Code language: SQL (Structured Query Language) (sql)
Output:
Table "public.employees"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('employees_id_seq'::regclass)
name | character varying(100) | | not null |
salary | numeric(10,2) | | not null |
Indexes:
"employees_pkey" PRIMARY KEY, btree (id)
Triggers:
after_delete_employee_trigger AFTER DELETE ON employees FOR EACH ROW EXECUTE FUNCTION archive_deleted_employee()
Code language: SQL (Structured Query Language) (sql)
The last part of the output shows the triggers of the employees
table.
Note that psql does not provide a command to list all triggers in the current database.
Summary
- List all triggers in a database or a specific table using the
information_schema.triggers
orpg_trigger
views. - Use psql command
\dS table_name
to display a table along with its associated triggers.