PostgreSQL: Dropping Triggers (DROP TRIGGER)
Remove triggers from PostgreSQL tables using DROP TRIGGER. This guide explains the syntax and usage of the command.
PostgreSQL DROP TRIGGER
What is the PostgreSQL DROP TRIGGER Command?
The DROP TRIGGER command removes an existing trigger from a PostgreSQL table.
Syntax
The syntax for dropping a trigger is:
Syntax
DROP TRIGGER [IF EXISTS] trigger_name
ON table_name [ CASCADE | RESTRICT ];
Parameters
| Parameter | Description |
|---|---|
trigger_name |
The name of the trigger to drop. |
IF EXISTS |
(Optional) If the trigger doesn't exist, PostgreSQL issues a notice instead of an error. |
table_name |
The name of the table the trigger is associated with. You can use a schema-qualified name (e.g., schema_name.table_name). |
CASCADE |
(Optional) Automatically drops any objects that depend on this trigger. |
RESTRICT |
(Optional) Prevents dropping the trigger if other objects depend on it (default behavior). |
Note: If you don't specify the table name, you must provide the full trigger name (including the table information).
Example: Dropping a Trigger
Let's create a trigger and then drop it. This example assumes you have an employee table.
Step 1: Creating a Function
First, we create a function that checks the length of an employee's name:
Creating a Function
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;
Step 2: Creating a Trigger
Next, we create a trigger that uses this function to check the employee name before inserts or updates:
Creating a Trigger
CREATE TRIGGER emp_name_check
BEFORE INSERT OR UPDATE
ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_emp_name();
Step 3: Dropping the Trigger
Finally, we drop the trigger:
Dropping a Trigger
DROP TRIGGER emp_name_check
ON employee;
Overview
We learned how to use the DROP TRIGGER command in PostgreSQL to remove triggers from tables.