How to Use the PostgreSQL DROP TRIGGER Statement
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.
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:
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)
Aug 01 14:51:20 user-UX330UAK systemd: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
If you need to start, stop or restart PostgreSQL server on a Windows machine, you can use the instructions shown below:
- First, open Control Panel
- Next, open Administrative Tools
- Open Services
- Find the PostgreSQL Server service
- Stop, Start or Restart the PostgreSQL service
What is DROP TRIGGER?
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:
IF EXISTSclause 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.
namespecifies the name of the trigger.
tablespecifies 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.
CASCADEwill drop or delete any objects that depend on the trigger.
RESTRICTis 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:
IF LENGTH(NEW.username) < 8 OR NEW.username IS NULL THEN
RAISE EXCEPTION 'Client username is less than eight (8) characters';
IF NEW.NAME IS NULL THEN
RAISE EXCEPTION 'Client username cannot be NULL';
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
UPDATE operation is performed on the table.
BEFORE INSERT OR UPDATE
FOR EACH ROW
EXECUTE PROCEDURE client_check();
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
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started