Article Overview

In this article, we will learn how to drop, alter, enable, and disable a PostgreSQL trigger. In the 1st part of this series, we got an overall view of database triggers, PostgreSQL-specific triggers, types of PostgreSQL triggers, and how to create a trigger with basic syntax and examples. If you haven't read it already, here's a link to the article: https://www.sqlservercentral.com/articles/postgresql-triggers-part-1

PostgreSQL Drop Trigger

The Drop Trigger command is used to remove an existing trigger from the database. A trigger can be dropped either via the PgAdmin GUI or via queries using PSql shell or PgAdmin's query editor as shown below:

From this menu, one can use the Delete/Drop option to drop the trigger only or one can use the Drop Cascade option to drop all associated objects at the same time in one go. For instance in this example there is one trigger function associated to this trigger which will also get dropped.

Basic Syntax

DROP TRIGGER [IF EXISTS] trigger_name 
ON table_name [ CASCADE | RESTRICT ];

In this syntax, the name of the trigger that is to be dropped is specified after the DROP TRIGGER [IF EXISTS] clause.  The IF EXISTS clause is used to avoid any unnecessary error which can result in attempting to delete any non existing trigger. Next the table_name is specified on which the trigger is active, followed by the condition CASCADE or RESTRICT. CASCADE is used to drop all objects associated with the trigger. RESTRICT prevents deletion of any associated object, and this is the default.

Let us drop the trigger we created in Part 1 of this tutorial,

DROP TRIGGER IF EXISTS stocks_trigger 
ON public."Stocks" CASCADE;

First, let us verify the trigger :

Lets us now drop the trigger and cross verify

PostgreSQL Alter Trigger

The Alter Trigger clause is used to modify properties of a trigger as shown below. We will look at renaming a trigger and using a trigger with an extension.

To rename a trigger, here is the syntax:

ALTER TRIGGER trigger_name
ON table_name 
RENAME TO new_trigger_name;

The syntax is pretty self explanatory. The ALTER TRIGGER clause is followed by old trigger name, followed by the table name, followed by new trigger name after the RENAME TO clause.

Let us see an example to rename a trigger

ALTER TRIGGER stocks_trigger
ON public."Stocks"
RENAME TO stocks_trigger_new;

We can also mark a trigger as dependent on an extension. An extension is used for bundling multiple SQL objects together in a single package that can be loaded or removed from the  database. Once extension is loaded into the database, it can function as built-in feature. Extension(s) can come out of box and they can be created too.

To mark the trigger as a dependent on extension, here is the syntax:

ALTER TRIGGER trigger_name ON table_name [ NO ] DEPENDS ON EXTENSION extension_name

In this syntax, the ALTER TRIGGER clause is specified followed by the trigger_name. Next the associated table_name is specified followed by the DEPENDS ON EXTENSION clause and extension_name.

In this example we will use PostgreSQL's built in extension, plpgsql, which is shown below:

ALTER TRIGGER stocks_trigger_new ON public."Stocks" DEPENDS ON EXTENSION plpgsql;

ENABLE/DISABLE TRIGGER

The Enable/Disable Trigger clause works in conjunction with Alter Table command.  They are used to enable/disable a trigger or all triggers associated with a table.

Basic Syntax:

ALTER TABLE table_name
ENABLE/DISABLE TRIGGER trigger_name |  ALL;

Example 1 - Let us disable the trigger we used in our last section

ALTER TABLE public."Stocks"
DISABLE TRIGGER stocks_trigger_new;

It is also possible to disable trigger through pdAdmin as shown below:

Disabling a trigger doesn't delete or drop it. The trigger still exists but it lies dormant. When a trigger is disabled, it is marked with a red cross in pgAdmin as shown below:

To prove trigger is disabled and has no effect no longer, let us insert one row of data in table "Stocks" and observe if the audit data gets inserted in the other table "stocks_audit" as explained in Part 1 of this tutorial.

INSERT INTO public."Stocks" 
VALUES (2,140,'Netflix');

Here we see there is no data corresponding to 'id 2' in the stocks_audit  table which proves the point the trigger wasn't executed on insertion in table 1.

Example 2 - Let us re-enable the trigger and insert one of row data in the Stocks table to verify its functionality.

ALTER TABLE public."Stocks"
ENABLE TRIGGER stocks_trigger_new;

Trigger can be also enabled via PgAdmin as shown below:

Let us now insert one row of data to re-validate its functionality,

INSERT INTO public."Stocks" 
VALUES (3,75000,'MRF');

Here we see there is data corresponding to 'id 3' in the stocks_audit table which proves the point the trigger is active and got triggered successfully on insertion in table 1.

Conclusion

This article presents an overview of the various DDL statements that can be performed on a trigger and how they can be handled via PgAdmin as well as Psql terminal.