How to List All Triggers in PostgreSQL

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.

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:

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 postgresCode 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 employeesCode 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 or pg_trigger views.
• Use psql command \dS table_name to display a table along with its associated triggers.