PL/pgSQL INSTEAD OF Triggers

Master INSTEAD OF triggers for making views writable — replace INSERT/UPDATE/DELETE on a view with custom DML on the underlying tables. Critical fixes: original DECLARE section was empty but body referenced an undeclared p_employee_id variable (hard error), and FK was created without ON DELETE CASCADE despite text claiming cascade behavior. Includes when to use INSTEAD OF vs PostgreSQL's automatic updatable views.

INSTEAD OF is a special trigger timing that exists only for views. Where BEFORE and AFTER triggers run alongside the underlying DML operation, INSTEAD OF triggers replace the operation entirely — the original INSERT, UPDATE, or DELETE never executes; only the trigger function does.

This is what makes views writable. By default, a view that joins multiple tables, applies aggregations, or otherwise transforms its source data is read-only — PostgreSQL doesn't know how to translate an UPDATE on the view back into UPDATEs on the underlying tables. INSTEAD OF triggers tell it.

INSTEAD OF triggers must be FOR EACH ROW — they don't make sense at statement level since the whole point is row-by-row translation. The trigger function typically returns NULL because the original DML doesn't run.

The classic INSTEAD OF use case: a view that joins two tables, where DML on the view should split into operations on each underlying table.

DROP VIEW  IF EXISTS employee_salaries;
DROP TABLE IF EXISTS salaries;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE salaries (
    employee_id    INT,
    effective_date DATE NOT NULL,
    salary         DECIMAL(10, 2) NOT NULL DEFAULT 0,
    PRIMARY KEY (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
);

INSERT INTO employees (name) VALUES ('Alice'), ('Bob');

INSERT INTO salaries VALUES
    (1, '2024-03-01', 60000.00),
    (2, '2024-03-01', 70000.00);

CREATE VIEW employee_salaries AS
SELECT e.employee_id,
       e.name,
       s.salary,
       s.effective_date
FROM   employees e
JOIN   salaries  s ON e.employee_id = s.employee_id;

Without an INSTEAD OF trigger, this view is read-only. SELECT * FROM employee_salaries works fine, but INSERT INTO employee_salaries ... will be rejected by PostgreSQL — joins between two tables aren't directly writable.

The function dispatches on TG_OP to handle INSERT, UPDATE, and DELETE differently:

CREATE OR REPLACE FUNCTION update_employee_salaries()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    p_employee_id INT;
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- Insert into employees first; capture the generated id
        INSERT INTO employees(name)
        VALUES (NEW.name)
        RETURNING employee_id INTO p_employee_id;

        -- Use that id to insert the salary row
        INSERT INTO salaries(employee_id, effective_date, salary)
        VALUES (p_employee_id, NEW.effective_date, NEW.salary);

        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        -- Just update the salary; we don't allow changing name via this view
        UPDATE salaries
        SET    salary = NEW.salary
        WHERE  employee_id    = NEW.employee_id
          AND  effective_date = NEW.effective_date;

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        -- Delete the employee — FK cascade removes salary rows
        DELETE FROM employees WHERE employee_id = OLD.employee_id;

        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER instead_of_employee_salaries
    INSTEAD OF INSERT OR UPDATE OR DELETE
    ON employee_salaries
    FOR EACH ROW
    EXECUTE FUNCTION update_employee_salaries();

Now the view is writable. An INSERT on employee_salaries doesn't actually run; the trigger handles it instead:

INSERT INTO employee_salaries (name, salary, effective_date)
VALUES ('Charlie', 75000.00, '2024-03-01');

-- Verify both underlying tables were updated
SELECT * FROM employees;
SELECT * FROM salaries;

One INSERT on the view, two INSERTs in the underlying tables — and the new employees.employee_id from the first INSERT was used as the FK in the second. The trigger replaced the view INSERT entirely.

UPDATE employee_salaries
SET    salary = 95000
WHERE  employee_id = 3;

SELECT * FROM salaries WHERE employee_id = 3;

DELETE FROM employee_salaries WHERE employee_id = 3;

-- Both tables should reflect the deletion (FK cascades from employees → salaries)
SELECT * FROM employees;
SELECT * FROM salaries;

Charlie is gone from both tables. The trigger only deleted from employees; the salary row was removed automatically by the FK's ON DELETE CASCADE.

PostgreSQL has another mechanism for making views writable: simple updatable views. If a view is just a SELECT from a single table with no aggregations, joins, DISTINCT, or other complications, PostgreSQL can automatically translate DML on the view into DML on the underlying table — no trigger needed.

Reach for INSTEAD OF only when the view's complexity exceeds what PostgreSQL can handle automatically.

1. Use RETURN NEW from INSERT/UPDATE handlers and RETURN OLD from DELETE handlers — convention for clarity, even though the return value of an INSTEAD OF trigger doesn't affect anything else.
2. Branch on TG_OP when handling multiple operations in one function. Keeps the dispatch logic in one place.
3. Capture generated IDs with RETURNING ... INTO when you insert into a table with a SERIAL primary key — needed to wire up the related row.
4. Declare local variables in DECLARE. Don't reference variables in the body that haven't been declared.
5. Prefer FK cascades over manual deletes when possible. ON DELETE CASCADE on the FK is declarative and robust; the trigger only handles what the FK can't.
6. INSTEAD OF triggers must be FOR EACH ROW — statement-level INSTEAD OF triggers don't have NEW/OLD records and rarely make sense.

• INSTEAD OF triggers replace DML operations on a view rather than running alongside them. The original INSERT/UPDATE/DELETE never executes; the trigger function does.
• They're how you make a non-trivially-writable view (with joins, aggregates, etc.) accept DML.
• Always FOR EACH ROW; can fire on INSERT, UPDATE, DELETE, or any combination.
• The function typically branches on TG_OP to dispatch per operation, then translates view-level changes into table-level DML.
• Use FK cascades and other declarative constraints alongside INSTEAD OF triggers — let the database handle what it can, and use the trigger only for the parts it can't.