Introduction to PostgreSQL DROP TRIGGER statement

To delete a trigger from a table, you use theDROP TRIGGER statement with the following syntax:

DROP TRIGGER [IF EXISTS] trigger_nameON table_name[ CASCADE | RESTRICT ];

In this syntax:

• First, specify the name of the trigger you want to delete after theDROP TRIGGER keywords.
• Next, useIF EXISTS to conditionally delete the trigger only if it exists. Deleting a non-existing trigger without specifying theIF EXISTS statement results in an error. If you useIF EXISTS to delete a non-existing trigger, PostgreSQL issues a notice instead. TheIF EXISTS is optional.
• Then, 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 such asschema_name.table_name.
• After that, use theCASCADE option to drop objects that depend on the trigger automatically. Note thatCASCADE option will also delete objects that depend on objects that depend on the trigger.
• Finally, use theRESTRICT option to refuse to drop the trigger if any objects depend on it. By default, theDROP TRIGGER statement usesRESTRICT.

In SQL standard, trigger names are not local to tables so theDROP TRIGGER statement does not have the table to which the trigger belongs:

DROP TRIGGER trigger_name;

PostgreSQL DROP TRIGGER statement example

First,create a function that validates the username of a staff. The username is not null and its length must be at least 8.

CREATE FUNCTION check_staff_user()    RETURNS TRIGGERAS $$BEGIN    IF length(NEW.username)< 8 OR NEW.usernameIS NULL THEN        RAISE EXCEPTION'The username cannot be less than 8 characters';    END IF;    IF NEW.NAMEIS NULL THEN        RAISE EXCEPTION'Username cannot be NULL';    END IF;    RETURN NEW;END;$$LANGUAGE plpgsql;

Second,create a new trigger on thestaff table of thesample database to check the username of a staff. This trigger will fire whenever you insert or update a row in thestaff table:

CREATE TRIGGER username_check    BEFORE INSERT OR UPDATEON staffFOR EACHROW    EXECUTE PROCEDURE check_staff_user();

Third, use theDROP TRIGGER statement to delete theusername_check trigger:

DROP TRIGGER username_checkON staff;

Summary

• Use the PostgreSQLDROP TRIGGER statement to delete a trigger from a table.

Last updated onMarch 30, 2024

On this page

• Introduction to PostgreSQL DROP TRIGGER statement
• PostgreSQL DROP TRIGGER statement example
• Summary