PostgreSQL DROP TRIGGER Statement

Summary: In this tutorial, you’ll learn how to remove a trigger from a table within your database using the PostgreSQL DROP TRIGGER statement.

Overview of the PostgreSQL DROP TRIGGER Statement #

The DROP TRIGGER statement removes a trigger from a specific table within your database.

Here’s the syntax of the DROP TRIGGER statement:

DROP TRIGGER [IF EXISTS] trigger_name
ON table_name
[CASCADE | RESTRICT];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the trigger after the DROP TRIGGER keywords.
• Second, use the IF EXISTS option to prevent an error from removing a trigger that does not exist in the database.
• Third, provide the name of the table to which the trigger belongs in the ON clause.
• Finally, use the CASCADE option to drop the dependent objects of the trigger automatically. The DROP TRIGGER statement uses the RESTRICT option by default, which rejects the removal if the trigger has any dependent objects.

PostgreSQL DROP TRIGGER Example #

First, create a new trigger function called prevent_negative_stock that ensures the stock level in the inventories table is never negative:

CREATE OR REPLACE FUNCTION prevent_negative_stock()
RETURNS TRIGGER 
AS 
$$
BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.quantity < 0) THEN
        RAISE EXCEPTION 'Stock level cannot be negative in warehouse % for product %',
            NEW.warehouse_id, NEW.product_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a trigger called inventory_stock_check for the inventories table, which executes the prevent_negative_stock() function before updating data in the inventories table:

CREATE TRIGGER inventory_stock_check 
BEFORE UPDATE ON inventories 
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_stock ();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, remove the inventory_stock_check trigger:

DROP TRIGGER IF EXISTS inventory_stock_check;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• Use the DROP TRIGGER statement to effectively remove a trigger from your database.

Quiz #