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:

sudo service postgresql start
  • To check if PostgreSQL is running, simply use the following command:
service postgresql status

You should see output that looks like this:

‚óè postgresql.service - PostgreSQL RDBMS
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[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: 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:

  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

Restarting PostgreSQL server in a Windows Machine

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:

CREATE FUNCTION client_check()
RETURNS TRIGGER
AS $$
BEGIN
IF LENGTH(NEW.username) < 8 OR NEW.username IS NULL THEN
RAISE EXCEPTION 'Client username is less than eight (8) characters';
END IF;
IF NEW.NAME IS NULL THEN
RAISE EXCEPTION 'Client username cannot be NULL';
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

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.

CREATE TRIGGER username_validate
BEFORE INSERT OR UPDATE
ON clients
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:

DROP TRIGGER username_validate
ON clients;

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.