PL/pgSQL Enabling and Disabling Triggers

Master ALTER TABLE DISABLE/ENABLE TRIGGER — temporarily suspend triggers without dropping them. Useful for bulk imports, backup restores, and debugging. Critical guidance: prefer USER over ALL (ALL includes FK constraint triggers); always wrap disable+work+enable in a transaction so failures don't leave triggers off; query pg_trigger to verify status. If you find yourself permanently disabling a trigger, DROP it instead.

You can temporarily turn a trigger off without dropping it. The trigger stays in the database, retaining its definition; it just won't fire on events. Re-enable it later and it picks up where it left off.

This is useful for:

• Bulk imports — disable audit/validation triggers during a one-time data load, then re-enable. Saves tremendous time.
• Maintenance windows — temporarily skip side effects (notifications, cascades) while doing bulk corrections.
• Debugging — turn off triggers one at a time to isolate which one is causing problems.
• Restore operations — replay a backup without re-firing audit triggers that already logged the changes.

DROP TABLE IF EXISTS employees_audit;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    salary       NUMERIC(10, 2) NOT NULL,
    last_updated TIMESTAMP
);

CREATE TABLE employees_audit (
    audit_id    SERIAL PRIMARY KEY,
    employee_id INT,
    name        VARCHAR(100),
    salary      NUMERIC(10, 2),
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (name, salary) VALUES
    ('Alice',   50000),
    ('Bob',     60000),
    ('Charlie', 55000);

-- Trigger 1: validation
CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER salary_check
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW EXECUTE FUNCTION check_salary();

-- Trigger 2: audit log
CREATE OR REPLACE FUNCTION log_employee_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO employees_audit (employee_id, name, salary)
    VALUES (NEW.id, NEW.name, NEW.salary);
    RETURN NEW;
END;
$$;

CREATE TRIGGER audit_employee_changes
    AFTER INSERT OR UPDATE ON employees
    FOR EACH ROW EXECUTE FUNCTION log_employee_change();

Two triggers on employees: a salary validator and an audit logger.

Turn off only the audit trigger — perhaps because we're about to do a bulk import that doesn't need to be logged:

ALTER TABLE employees
    DISABLE TRIGGER audit_employee_changes;

Now updates to employees fire the salary check (still active) but skip audit logging:

UPDATE employees SET salary = 65000 WHERE id = 1;
SELECT * FROM employees_audit;   -- empty: no audit row added

The salary check still runs (because it's still enabled) but the audit logger doesn't.

ALTER TABLE employees
    ENABLE TRIGGER audit_employee_changes;

UPDATE employees SET salary = 67000 WHERE id = 1;
SELECT * FROM employees_audit;

The pattern for bulk imports: disable user-defined triggers, do the import, re-enable. Wrap in a transaction so a partial failure can't leave triggers off:

BEGIN;
    -- USER excludes constraint-enforcing triggers (FKs, etc.)
    ALTER TABLE employees DISABLE TRIGGER USER;

    -- Do the bulk insert; no audit rows created, no salary checks run
    INSERT INTO employees (name, salary)
    SELECT name, salary FROM staging_employees;

    -- Re-enable
    ALTER TABLE employees ENABLE TRIGGER USER;
COMMIT;

Two key decisions:

• DISABLE TRIGGER USER — turns off our triggers but leaves FK enforcement on. Critical for keeping the database consistent during the import.
• Wrapped in BEGIN ... COMMIT — if anything fails inside, the entire transaction rolls back including the disable. We never leave triggers off accidentally.

To see which triggers are currently enabled or disabled, query the system catalog:

SELECT tgname  AS trigger_name,
       tgrelid::regclass AS on_table,
       CASE tgenabled
         WHEN 'O' THEN 'enabled'
         WHEN 'D' THEN 'disabled'
         WHEN 'R' THEN 'enabled (replica only)'
         WHEN 'A' THEN 'enabled (always)'
       END AS status
FROM   pg_trigger
WHERE  NOT tgisinternal           -- skip system-internal triggers
ORDER  BY tgrelid::regclass::text, tgname;

1. Wrap disable+work+enable in a transaction. A failure inside should roll back everything, including the disable — otherwise you leave triggers off and forget.
2. Prefer USER over ALL when disabling. ALL includes FK constraint triggers; turning those off is dangerous.
3. Don't use disable as a permanent solution. If you don't want a trigger to fire ever again, drop it. A permanently-disabled trigger is confusing — readers see it exists and assume it does something.
4. Verify with the catalog after critical operations. Query pg_trigger to confirm everything is back on. Don't trust that your script ran the re-enable.
5. Document why a trigger is disabled. If you must leave one off temporarily, comment it on the table so the next person knows what's going on.
6. Be aware of replication-related options. ENABLE REPLICA TRIGGER and ENABLE ALWAYS TRIGGER control behavior under streaming replication — usually irrelevant for application work but useful to know exist.

• ALTER TABLE ... DISABLE TRIGGER ... turns a trigger off without deleting it; ENABLE TRIGGER turns it back on.
• You can disable a single trigger by name, all user-defined triggers (USER), or every trigger on the table including FK enforcement (ALL).
• Prefer USER to ALL — keeps foreign keys enforced.
• Use disable+enable for one-off operations like bulk imports, backup restores, or controlled maintenance. Always wrap in a transaction.
• Don't use disable as a permanent state. If a trigger should never fire again, drop it.
• Query pg_trigger to inspect trigger status: the tgenabled column codes the state (O = enabled, D = disabled, etc.).