In PostgreSQL, the DROP TRIGGER statement is used to drop a trigger from a table.
Syntax: DROP TRIGGER [IF EXISTS] trigger_name ON table_name [ CASCADE | RESTRICT ];
Let’s analyze the above syntax:
- First, specify the name of the trigger which you want to delete after the DROP TRIGGER keywords.
- Second, use IF EXISTS to conditionally delete the trigger only if it exists. Attempt to delete a non-existing trigger without specifying the IF EXISTS statement results in an error. If you use IF EXISTS to delete a non-existing trigger, PostgreSQL issues a notice instead.
- Third, specify the name of the table to which the trigger belongs. If the table belongs to a specific schema, you can use the schema-qualified name of the table.
- Fourth, use the CASCADE option if you want to drop objects that depend on the trigger automatically. Note that the CASCADE option will also delete objects that depend on objects that depend on the trigger.
- Fifth, use RESTRICT TO refuse to drop the trigger if any objects depend on it. By default, the DROP TRIGGER statement uses RESTRICT.
First, create a function that validates the username of a staff. The username of staff must not be null and its length must be at least 8.
CREATE FUNCTION check_staff_user() RETURNS TRIGGER AS $$ BEGIN IF length(NEW.username) < 8 OR NEW.username IS NULL THEN RAISE EXCEPTION 'The username cannot be less than 8 characters'; END IF; IF NEW.NAME IS NULL THEN RAISE EXCEPTION 'Username cannot be NULL'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
Second, create a new trigger on the staff table to check the username of a staff. This trigger will fire whenever you insert or update a row in the staff table.
CREATE TRIGGER username_check BEFORE INSERT OR UPDATE ON staff FOR EACH ROW EXECUTE PROCEDURE check_staff_user();
This will result in the following trigger creation:
Third, use the DROP TRIGGER statement to delete the username_check trigger:
DROP TRIGGER username_check ON staff;
- PostgreSQL - CREATE TRIGGER
- PostgreSQL - ALTER TRIGGER
- PostgreSQL - Disabling a Trigger
- PostgreSQL - Trigger
- PostgreSQL - DROP DATABASE
- PostgreSQL - DROP COLUMN
- PostgreSQL - DROP TABLE
- PostgreSQL - DROP SCHEMA
- PostgreSQL - Drop Procedure
- PostgreSQL - Drop Function
- PostgreSQL - DROP INDEX
- PostgreSQL - DROP ROLE
- PostgreSQL - DROP TABLESPACE
- PostgreSQL - Connect To PostgreSQL Database Server in Python
- What is PostgreSQL - Introduction
- Install PostgreSQL on Windows
- Install PostgreSQL on Mac
- PostgreSQL - Loading a Database
- PostgreSQL - DISTINCT ON expression
- PostgreSQL - SELECT
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.