How to Use the PostgreSQL DROP TRIGGER Statement

Introduction

If you’re using triggers in PostgreSQL, there will likely be times when you want to remove one from use. Fortunately, it’s easy to remove a trigger with the help of the DROP TRIGGER statement. In this article, we’ll show you how to drop a trigger using DROP TRIGGER in PostgreSQL.

Prerequisites

Before we delve into the details of this tutorial, let’s take a moment to go over a couple of basic prerequisites that should be in place for this task:

• You should already have a basic understanding of how to create a PostgreSQL trigger.

• You’ll need to make sure that PostgreSQL server is properly installed, configured and running in the background on your machine. To start up PostgreSQL server on a LINUX machine, you can use the command shown below:

• To check if PostgreSQL is running, simply use the following command:

You should see output that looks like this:

If you need to start, stop or restart PostgreSQL server on a Windows machine, you can use the instructions shown below:

1. First, open Control Panel
2. Next, open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Stop, Start or Restart the PostgreSQL service

What is DROP TRIGGER?

The DROP TRIGGER statement simply removes a current trigger definition. To execute this statement, the client needs to be the owner of the table for which the trigger was created.

The DROP TRIGGER statement has the following format:

DROP TRIGGER [ IF EXISTS ] name ON table [ CASCADE | RESTRICT ]

Let’s take a closer look at each part of this statement to understand it better:

• The IF EXISTS clause ensures that the trigger is deleted only if it exists. Trying to delete a non-existent trigger without using this parameter will throw an error.

• name specifies the name of the trigger.

• table specifies the name of the table to which the trigger is bound. You can use the schema-qualified name of the table if the table belongs to a specific schema.

• Using CASCADE will drop or delete any objects that depend on the trigger.

• When RESTRICT is used, the trigger will not be dropped if any objects depend upon it.

NOTE: The DROP TRIGGER statement in PostgreSQL is incompatible with the SQL standard. The difference is important: In the SQL standard, trigger names are not local to tables, so the command syntax is simply DROP TRIGGER name.

PostgreSQL DROP TRIGGER example

If we want to execute a DROP TRIGGER in PostgreSQL, we first need to create a function. For the purposes of this tutorial, we’ll assume you’re already familiar with the process of creating a function. An example of a statement that creates a function is shown below:

Now that we have a working function, we can create a trigger on the client’s username within the clients table. This trigger will be fired whenever an INSERT or UPDATE operation is performed on the table.

Let’s imagine that we want to remove this trigger. We can now use the DROP TRIGGER statement to delete the username_validate trigger. You can see how it’s done in the following statement:

The statement shown above will drop the username_validate trigger in the clients table.

Conclusion

When you’re working with triggers, it’s important to know how to both create them and delete them. In this article, we learn how to use DROP TRIGGER in PostgreSQL to remove a trigger. With the step-by-step instructions outlined in this tutorial, you’ll have no trouble removing triggers from your own PostgreSQL database.