How to Disable Trigger in PostgreSQL: Syntax and Examples

In PostgreSQL, you can disable a trigger using the ALTER TABLE table_name DISABLE TRIGGER trigger_name; command. To re-enable it, use ALTER TABLE table_name ENABLE TRIGGER trigger_name;. This temporarily stops the trigger from firing without deleting it.

Syntax

The basic syntax to disable or enable a trigger in PostgreSQL is:

• ALTER TABLE table_name DISABLE TRIGGER trigger_name; - disables the specified trigger on the table.
• ALTER TABLE table_name ENABLE TRIGGER trigger_name; - enables the specified trigger again.
• You can also disable or enable ALL triggers on a table using DISABLE TRIGGER ALL or ENABLE TRIGGER ALL.

sql

ALTER TABLE table_name DISABLE TRIGGER trigger_name;
ALTER TABLE table_name ENABLE TRIGGER trigger_name;

Example

This example shows how to disable and then enable a trigger named my_trigger on a table called employees. It demonstrates stopping the trigger from firing during an update and then restoring it.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  salary INT
);

CREATE FUNCTION log_salary_change() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Salary changed for employee %', NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
  BEFORE UPDATE ON employees
  FOR EACH ROW
  EXECUTE FUNCTION log_salary_change();

-- Disable the trigger
ALTER TABLE employees DISABLE TRIGGER my_trigger;

-- Update without trigger firing
UPDATE employees SET salary = salary + 1000 WHERE id = 1;

-- Enable the trigger again
ALTER TABLE employees ENABLE TRIGGER my_trigger;

Output

NOTICE:  Salary changed for employee 1

Common Pitfalls

Common mistakes when disabling triggers include:

• Trying to disable a trigger that does not exist, which causes an error.
• Forgetting to re-enable triggers after disabling them, which can cause unexpected behavior.
• Using DISABLE TRIGGER ALL without realizing it disables system triggers too, which can break replication or constraints.

Always check trigger names carefully and re-enable triggers when done.

sql

/* Wrong: disabling a non-existent trigger */
ALTER TABLE employees DISABLE TRIGGER non_existent_trigger;

/* Right: check trigger exists before disabling */
-- Use psql or query pg_trigger catalog to verify trigger name

/* Wrong: disabling all triggers without caution */
ALTER TABLE employees DISABLE TRIGGER ALL;

/* Right: disable only specific triggers when possible */
ALTER TABLE employees DISABLE TRIGGER my_trigger;

Quick Reference

Command	Description
ALTER TABLE table_name DISABLE TRIGGER trigger_name;	Disable a specific trigger on a table
ALTER TABLE table_name ENABLE TRIGGER trigger_name;	Enable a specific trigger on a table
ALTER TABLE table_name DISABLE TRIGGER ALL;	Disable all triggers on a table (use with caution)
ALTER TABLE table_name ENABLE TRIGGER ALL;	Enable all triggers on a table

Key Takeaways

Use ALTER TABLE ... DISABLE TRIGGER to temporarily stop a trigger from firing.

Always re-enable triggers after disabling them to avoid unexpected behavior.

Disabling ALL triggers affects system triggers and can cause issues; prefer disabling specific triggers.

Verify trigger names before disabling to prevent errors.

Disabling triggers does not delete them; it only pauses their execution.