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:

DROP TRIGGER [IF EXISTS] trigger_name 
ON table_name [ CASCADE | RESTRICT ];

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.

CREATE FUNCTION check_emp_name()
    RETURNS TRIGGER
AS $$
BEGIN
IF length(NEW.emp_name) < 10 OR NEW.emp_name IS NULL THEN
  RAISE EXCEPTION 'The emp_name cannot be less than 10 characters';
    END IF;
    IF NEW.emp_nAME IS NULL THEN
        RAISE EXCEPTION 'emp_name cannot be NULL';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

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.

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):

CREATE TRIGGER emp_name_check 
BEFORE INSERT OR UPDATE
ON employee
FOR EACH ROW 
EXECUTE PROCEDURE check_emp_name();

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.

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.

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:

DROP TRIGGER emp_name_check
ON employee;

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.