PostgreSQL Tutorial: List Triggers

Summary: Triggers are the functions that are fired/invoked when a certain specified event occurs. Sometimes we need to list the triggers in PostgreSQL to know which triggers and how many triggers are associated to a table. In this article, we will look at the methods to list down all the triggers in our database and also list all the triggers related to a table.

Table of Contents

How to List Triggers in PostgreSQL Database

There are three ways to list down triggers in PostgreSQL. These methods are:

● Method 1: Using information_schema.triggers.

● Method 2: Using psql.

● Method 3: Using pg_trigger catalog.

Let’s learn about all 3 of these methods one by one.

Method 1: Using information_schema.triggers

To list all the triggers associated with a database, we need to refer to “event_object_table”. This is the table where the triggers are defined. We also need to refer to the “information_schema”, which provides us with information about our schema and tables or simply our current database. We can access some information about our tables and their metadata through information_schema. We will get information about triggers from the information schema. The query will look like this:

SELECT event_object_table AS tab_name, trigger_name
 FROM information_schema.triggers
 GROUP BY tab_name, trigger_name
 ORDER BY tab_name, trigger_name ;

This query will give us information about all the triggers in our database along with their associated tables. Currently, my database contains only three tables that have triggers so this will be the output of the above query:

Now if we want to list the triggers associated with a specific table, we will just have to add a where statement to specify the name of that particular table. The query will be customized like this:

SELECT event_object_table AS tab_name, trigger_name
 FROM information_schema.triggers
 WHERE event_object_table ='project_status'
 GROUP BY tab_name, trigger_name
 ORDER BY tab_name, trigger_name;

This query will give us all the triggers associated with the table ”project_status”. In my case, it had only one trigger so only one was enlisted in the output.

This was all about the first method, in the second method we will see how can we do the same task using psql.

Method 2: Using psql

We can also get the list of triggers associated with a table using psql. We can view all the triggers associated with a table by running the \dS command in psql tool. The following are the steps:

Step 1:

First of all, open psql in your local system. We will be connecting to our database where we want to list a table’s triggers. Enter your password when it asks for “Password for user postgres”. you will see the following:

Step 2:

Now write the command as “\dS your table name”. In my case, it is “\dS project_status”. So this will give all the triggers related to the table.

You can see on the very last line, that is the trigger associated to this table.

Method 3: Using pg_trigger Catalog

The third and last method for getting the list of triggers associated with a specific table is using pg_trigger catalog. “Pg_trigger catalog” keeps and stores triggers on the tables. To get the triggers for a table using the pg_trigger catalog we write the following query:

SELECT 
  tgname AS trig_name
 FROM 
  pg_trigger
 WHERE
  tgrelid = 'project_status'::regclass
 ORDER BY
  trig_name;

“tgname” is the trigger name. ”tgrelid” refers to the table the trigger is on and the name of the table needs to be specified after that. The output of the query gives the triggers associated with the table ”project_status”.

These are the three ways we can list the triggers for our database tables.

Conclusion

In this tutorial, we have enlisted 3 ways to list the triggers in our database. In the first method by using the information_schema.triggers we can list the triggers associated with any table. The second method was using the psql command and lastly, we saw the method to list the triggers using the pg_trigger catalog. All these methods help us in getting the triggers.