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:
DROP TRIGGER trigger_name; 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.
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.
Leave a Reply