PostgreSQL Disable Triggers

Summary: In this tutorial, you’ll learn how to use the PostgreSQL ALTER TABLE ... DISABLE TRIGGER statement to disable a trigger.

How to disable triggers #

Sometimes, you may want to turn off a trigger. For example, triggers may cause significant overhead when performing bulk inserts due to additional operations for each inserted row.

To disable a trigger, you can use the ALTER TABLE DISABLE TRIGGER statement with the following syntax:

ALTER TABLE table_name
DISABLE TRIGGER trigger_name | ALL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the table name to which the trigger belongs in the ALTER TABLE clause.
• Second, provide the trigger name you want to disable in the DISABLE TRIGGER clause. If you want to turn off all table triggers, you can use the ALL keyword instead of disabling the trigger individually.

Disabling Trigger Example #

First, create a table to log the price change in the products table:

CREATE TABLE price_change_logs (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(11,2) NOT NULL,
    new_price DECIMAL(11, 2) NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a trigger function that logs the price changes into the price_change_logs table:

CREATE OR REPLACE FUNCTION log_price_changes()
RETURNS TRIGGER 
AS 
$$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_change_logs (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, create a trigger that executes the log_price_changes function after updating the price in the products table:

CREATE TRIGGER price_update_trigger
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_changes();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, update the price of the product with id 1 to 799.99:

UPDATE products
SET price = 799.99
WHERE product_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The update statement causes the trigger price_update_trigger to fire, which executes the log_price_changes function. The function logs the price change into the price_change_logs table.

Fifth, retrieve the data from the price_change_logs table:

SELECT
  product_id,
  old_price,
  new_price
FROM
  price_change_logs;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | old_price | new_price
------------+-----------+-----------
          1 |    999.99 |    799.99

Sixth, disable the trigger price_update_trigger of the products table:

ALTER TABLE products
DISABLE TRIGGER price_update_trigger;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Seventh, update the price of product id 2 to 999.99:

UPDATE products
SET price = 999.99
WHERE product_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Eighth, retrieve data from the price_change_logs table:

SELECT
  product_id,
  old_price,
  new_price
FROM
  price_change_logs
WHERE
  product_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It does not have log for the product id 2 because the trigger was disable.

Finally, disable all triggers of the products table:

ALTER TABLE products 
DISABLE TRIGGER ALL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Enabling triggers #

To enable a trigger, you use the ALTER TABLE ... ENABLE TRIGGER statement:

ALTER TABLE table_name
ENABLE TRIGGER trigger_name | ALL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, provide the table name to which the trigger belongs in the ALTER TABLE clause.
• Second, specify the trigger name you want to enable in the ENABLE TRIGGER clause. You can use the ALL keyword to enable all triggers of a table.

For example, the following statement enables the trigger price_update_trigger of the products table:

ALTER TABLE products
ENABLE TRIGGER price_update_trigger;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The following example enables all triggers of the products table:

ALTER TABLE products
ENABLE TRIGGER price_update_trigger;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• Use the PostgreSQL ALTER TABLE ... DISABLE TRIGGER statement to disable a trigger.
• Use the PostgreSQL ALTER TABLE ... ENABLE TRIGGER statement to enable a trigger.