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:
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:
1 2 | 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
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. TheRESTRICT
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:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE FUNCTION name_changes_log() RETURNS TRIGGER AS $BODY$ BEGIN IF NEW.name <> OLD.name THEN INSERT INTO kids_audit(kids_id,kids_name,modified_on) VALUES(OLD.id,OLD.name,now()); END IF; RETURN NEW; END; $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:
1 2 3 4 5 | CREATE TRIGGER name_last_modified BEFORE UPDATE ON kids 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:
1 2 | DROP TRIGGER IF EXISTS name_last_modified ON kids |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started