1. Home
  2. DROP trigger

PostgreSQL DROP TRIGGER

In this section, we are going to understand the working of the PostgreSQL DROP TRIGGER command and see the example of dropping and deleting a trigger from a specified table in PostgreSQL.

What is PostgreSQL Drop Trigger command?

In PostgreSQL, we can use the Drop Trigger command to remove the existing trigger.

The syntax of the PostgreSQL Drop trigger command

The following illustration is used to drop a trigger from a particular table:

In the above syntax, we have used the following parameters:

Parameters Description
Trigger_name
  • It is used to define the trigger name that we need to remove, and it is mentioned after the DROP TRIGGER keyword.
If EXISTS
  • The If EXISTS parameter is used to remove the trigger temporarily only if it exists.
  • And if we try to remove a non-existing trigger without specifying the IF EXISTScommand, we will get an error in the result.
  • PostgreSQL issues a notice as an alternative if we use the IF EXISTS to remove a non-existing trigger.
Table_name
  • The table name parameter is used to define the table name where the trigger belongs.
  • If the table is linked to a defined schema, we can use the table's schema-qualified name, such as schema_name.table_name.
CASCADE
  • If we want to drop objects, which automatically rely on the trigger, we can use the CASCADE option.
RESTRICT
  • We can use the RESTRICT option if any objects depend on trigger or we want to refuse or drop that trigger.
  • The DROP TRIGGER command uses the RESTRICT option by default.

Note: In SQL, the trigger names are not limited to tables, therefore, we can use the below command:

Example of PostgreSQL Drop Trigger command

Let us see a simple example to understand the working of the PostgreSQL DROP Trigger command.

For this, we are taking the Employee table, which we created in the earlier section of the PostgreSQL tutorial.

Step1: Creating a new function

Firstly, we will create a function, which checks the employee's emp_name, where the name of the employee length must be at least 10 and must not be null.

Output

On executing the above command, we will get the following message: the check_emp_name() function has been created successfully into the Organization database.

PostgreSQL DROP Trigger

Step2: Creating a new Trigger

After creating the check_emp_name() function, we will create a new trigger on the employee table to check an employee's emp_name.

And the same trigger will be executed whenever we update or insert a row in the Employee table (taken from the Organization database):

Output

After implementing the above command, we will get the following message window, which displays that the emp_name_check trigger has been inserted successfully for the Employee table.

PostgreSQL DROP Trigger

And, we can also verify that the above created function(check_emp_name()) and trigger(emp_name_check) in the object tree of Organization database.

PostgreSQL DROP Trigger

Step3: Dropping a trigger

Once the function and trigger have been generated successfully, we will remove the emp_name_check trigger with the help of the DROP TRIGGER command, as shown below:

Output

After implementing the above command, we will get the below output, which displays that the particular trigger has been dropped successfully from the Employee table.

PostgreSQL DROP Trigger

Overview

In the PostgreSQL Drop Trigger section, we have learned the following topics:

  • We understand the PostgreSQL drop trigger command usage, which is used to remove the particular table's trigger.
Next TopicPostgreSQL ALTER TRIGGER




Hot Tutorials

Contact US

Email:[email protected]

DROP trigger
10/30