Drop Postgres Triggers
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.
Be sure to install and configure the following before proceeding with the tutorial:
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:
DROP TRIGGER [IF EXISTS] the_trigger_name
ON the_table_name [CASCADE | RESTRICT];
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
We then add the conditional statement
IF EXISTSto 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
CASCADEoption to drop any objects that are dependent on the specified trigger.
Alternatively, we can use the
RESTRICToption to prevent the deletion of a trigger if it has any dependencies. The
RESTRICToption 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:
CREATE OR REPLACE FUNCTION name_changes_log()
RETURNS TRIGGER AS
IF NEW.name <> OLD.name THEN
INSERT INTO kids_audit(kids_id,kids_name,modified_on)
$BODY$ LANGUAGE plpgsql;
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:
CREATE TRIGGER name_last_modified
FOR EACH ROW
EXECUTE PROCEDURE name_changes_log();
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:
DROP TRIGGER IF EXISTS name_last_modified
After removing the trigger, Postgres will respond with the message ‘DROP TRIGGER’. This will confirm that we have successfully deleted the specified trigger.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started