PL/pgSQL ALTER TRIGGER

Master ALTER TRIGGER and trigger replacement — the only thing ALTER TRIGGER does is rename, so changing logic means editing the function (CREATE OR REPLACE FUNCTION) and changing structure means drop-and-recreate. Critical fixes: original error message was missing the word 'be', had no division-by-zero guard, contained a stray copy-pasted 'Negative' comment, and the CREATE TRIGGER statement was missing a trigger name entirely.

ALTER TRIGGER in PostgreSQL is more limited than you might expect: it does one thing — rename a trigger. It cannot change the trigger's timing, event, target table, function, or any other property. To modify those, you must drop and recreate.

The good news: most "edits" you'll want to make are actually edits to the trigger function, and those don't require touching the trigger at all. CREATE OR REPLACE FUNCTION updates the function in place; the existing trigger automatically picks up the new logic.

You must own the table to alter its triggers — same permission requirement as DROP.

DROP TABLE IF EXISTS employees CASCADE;

CREATE TABLE employees (
    employee_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    salary      NUMERIC(11, 2) NOT NULL DEFAULT 0
);

First, create a trigger function that prevents salary from doubling in a single update — a common business rule. Then bind it to the employees table:

CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Guard against division by zero and NULLs
    IF OLD.salary IS NULL OR OLD.salary = 0 THEN
        RETURN NEW;
    END IF;

    IF (NEW.salary - OLD.salary) / OLD.salary >= 1 THEN
        RAISE EXCEPTION 'Salary increase of 100%% or more is not allowed (% -> %)',
                        OLD.salary, NEW.salary;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER before_update_salary
    BEFORE UPDATE
    ON employees
    FOR EACH ROW
    EXECUTE FUNCTION check_salary();

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 100000);

-- Try to double the salary — should be refused
UPDATE employees
SET    salary = 200000
WHERE  employee_id = 1;

The trigger fired and aborted the update. The transaction rolled back; no data changed.

Now use ALTER TRIGGER to rename before_update_salary to something more descriptive:

ALTER TRIGGER before_update_salary
    ON employees
    RENAME TO salary_change_guard;

The trigger still fires the same function on the same table at the same timing — only the name changed.

PostgreSQL has no CREATE OR REPLACE TRIGGER statement — that pattern that works for functions doesn't exist for triggers. To swap one trigger for another, drop and recreate:

Wrap the drop+create in a transaction so observers either see the old trigger or the new trigger — never neither:

-- A different validator function, perhaps with stricter rules
CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;

    IF NEW.salary > 1000000 THEN
        RAISE EXCEPTION 'Salary above 1,000,000 requires manual approval';
    END IF;

    RETURN NEW;
END;
$$;

BEGIN;
    DROP TRIGGER IF EXISTS salary_change_guard ON employees;

    CREATE TRIGGER salary_change_guard
        BEFORE UPDATE
        ON employees
        FOR EACH ROW
        EXECUTE FUNCTION validate_salary();
COMMIT;

If we only want to change the validation logic, just replace the function. The existing trigger keeps firing with the new behavior:

-- The trigger salary_change_guard still exists and still calls validate_salary().
-- This redefines the function — the trigger automatically uses the new logic.
CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;

    IF NEW.salary > 500000 THEN
        RAISE EXCEPTION 'Salary above 500,000 requires manual approval';
    END IF;

    -- New rule: round to nearest hundred
    NEW.salary := ROUND(NEW.salary / 100) * 100;

    RETURN NEW;
END;
$$;

No DROP TRIGGER, no recreation, no transaction needed. The trigger immediately starts using the new function body for subsequent updates.

1. To change trigger behavior, edit the function with CREATE OR REPLACE FUNCTION. The trigger doesn't need to be touched.
2. To change trigger structure (timing, event, table), drop and recreate inside a transaction.
3. Use DROP TRIGGER IF EXISTS + CREATE TRIGGER together in deployment scripts. PostgreSQL has no CREATE OR REPLACE TRIGGER.
4. Wrap drop-and-recreate in a transaction so concurrent sessions never see the table in a "no trigger" state.
5. Guard trigger functions against bad input. Division by zero, NULL OLD/NEW columns, missing rows — all real and worth handling.
6. Use %% in RAISE format strings to emit a literal percent sign. A bare % consumes an argument from the parameter list.

• ALTER TRIGGER only renames a trigger — nothing else. To change behavior, edit the function; to change structure, drop and recreate.
• Syntax: ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;
• PostgreSQL has no CREATE OR REPLACE TRIGGER. Use DROP TRIGGER IF EXISTS + CREATE TRIGGER, ideally in a transaction.
• If the goal is to change trigger logic, modify the underlying function with CREATE OR REPLACE FUNCTION — no trigger changes required.
• Always guard trigger functions against edge cases: NULLs, zeros, rows that don't exist. The trigger fires on every change; bad input means a runtime error on every change.