Drop Postgres Triggers

Introduction

In PostgreSQL, a trigger is a function that’s executed automatically when a certain event occurs. Triggers can be attached to objects such as tables and views. It’s important to know how to remove a trigger from its associated object if you no longer want to use it. In this article, we’ll explain how to drop Postgres triggers and provide some examples to get you started.

Prerequisite

Be sure to install and configure the following before proceeding with the tutorial:

• PostgreSQL

You should also have some basic knowledge of PostgreSQL to be able to follow along with the examples presented in this article.

How to Drop a PostgreSQL Trigger

To remove or delete a trigger that is associated with a table, we use the statement DROP TRIGGER. The syntax is shown below:

Let’s take a closer look at this statement and discuss each component of it:

• First, we specify the name of the trigger that we want to remove using the phrase DROP TRIGGER.

• We then add the conditional statement IF EXISTS to ensure that we won’t encounter any error or exception by trying to delete a trigger that doesn’t actually exist. This type of error can occur if you accidentally misspell the name of the trigger. Instead of returning an error, Postgres will just issue a notice.

• Next, we provide the name of the target table with which the trigger is associated.

• We use the CASCADE option to drop any objects that are dependent on the specified trigger.

• Alternatively, we can use the RESTRICT option to prevent the deletion of a trigger if it has any dependencies. The RESTRICT option is enabled by default.

DROP TRIGGER Example

In this section, we’ll show you some examples that demonstrate how to remove a trigger using the DROP TRIGGER statement.

Let’s start by creating a function called name_changes_log(). This function will log any changes made to the ‘name’ field of a given record:

Next, we’ll create a trigger on the kids table. The BEFORE UPDATE clause indicates that the trigger will be invoked before any updates to the table:

We can see a visual rendering of the above steps using the Postgres GUI pgAdmin4. To view it, simply connect to your server and locate your database and your table in the GUI.

Shown below is a graphical representation of the above steps:

We can see that there is one trigger under the kids table named name_last_modified— this is the trigger we described in the previous section.

In the ‘Trigger Functions’ section, we can see that there is one available named name_changes_log(); again, this is the one that was defined in the previous section.

At this point, we can perform the DROP TRIGGER action against the specified trigger. To do this, we’ll use the following syntax:

After removing the trigger, Postgres will respond with the message ‘DROP TRIGGER’. This will confirm that we have successfully deleted the specified trigger.

Conclusion

Triggers can serve as an effective way to maintain data integrity among your PostgreSQL tables and store business rules within your database. If you’re using triggers in your PostgreSQL environment, it’s important to know how to remove a trigger from a given table or view. In this article, we explained how to drop PostgreSQL triggers using the simple DROP TRIGGER statement, and we provided some examples to demonstrate how it works. With our examples to get you started, you’ll be ready to manage and delete triggers in your own PostgreSQL database.