PostgreSQL 39P01 Error: Causes and Solutions Complete Guide

PostgreSQL Error 39P01: trigger protocol violated

The 39P01 trigger protocol violated error occurs when a trigger function in PostgreSQL fails to comply with the internal trigger calling protocol. This typically happens when a trigger function returns the wrong type, is called directly instead of through a trigger event, or omits a required RETURN statement. Understanding this error is essential for any developer writing PL/pgSQL trigger functions.

---

Top 3 Causes

1. Trigger Function Declared with Wrong Return Type

A trigger function must be declared with RETURNS trigger. Using any other return type such as void, integer, or a table type will cause PostgreSQL to raise this error immediately when the trigger fires.

-- ❌ Wrong: trigger function returning void
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'fired';
    -- No RETURN NEW = protocol violation
END;
$$ LANGUAGE plpgsql;

-- ✅ Correct: must use RETURNS trigger and RETURN NEW/OLD/NULL
CREATE OR REPLACE FUNCTION good_trigger()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW; -- mandatory for BEFORE row-level triggers
END;
$$ LANGUAGE plpgsql;

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

2. Calling a Trigger Function Directly

Trigger functions are designed to be invoked only by the PostgreSQL trigger mechanism, not directly by users or application code. When called directly, the required trigger context variables (TG_OP, NEW, OLD, etc.) do not exist, causing a protocol violation.

-- ❌ Wrong: calling trigger function directly
SELECT good_trigger(); -- raises ERROR: 39P01

-- ✅ Correct: separate reusable business logic into a regular function
CREATE OR REPLACE FUNCTION log_audit(tbl TEXT, op TEXT)
RETURNS void AS $$
BEGIN
    INSERT INTO audit_log(table_name, operation, logged_at)
    VALUES (tbl, op, NOW());
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS trigger AS $$
BEGIN
    PERFORM log_audit(TG_TABLE_NAME, TG_OP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Call the regular function directly (safe)
SELECT log_audit('employees', 'MANUAL');

3. Missing or Incorrect RETURN in Row-Level Triggers

For FOR EACH ROW triggers, the return value matters. A BEFORE row-level trigger must return NEW, OLD, or NULL (returning NULL cancels the DML operation). Omitting the RETURN statement entirely or returning an incompatible value violates the protocol.

-- ❌ Wrong: missing RETURN in a BEFORE trigger
CREATE OR REPLACE FUNCTION missing_return_trigger()
RETURNS trigger AS $$
BEGIN
    NEW.salary := GREATEST(NEW.salary, 0);
    -- forgot RETURN NEW!
END;
$$ LANGUAGE plpgsql;

-- ✅ Correct: always explicitly return in row-level triggers
CREATE OR REPLACE FUNCTION salary_check_trigger()
RETURNS trigger AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative: %', NEW.salary;
    END IF;
    NEW.salary := ROUND(NEW.salary, 2);
    RETURN NEW; -- required for BEFORE row-level trigger
END;
$$ LANGUAGE plpgsql;

-- For AFTER row-level triggers, return NULL (return value is ignored anyway)
CREATE OR REPLACE FUNCTION after_insert_trigger()
RETURNS trigger AS $$
BEGIN
    INSERT INTO notifications(message) VALUES ('New row inserted: ' || NEW.id::TEXT);
    RETURN NULL; -- best practice for AFTER triggers
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Solutions

• Always declare trigger functions with RETURNS trigger
• Never call trigger functions directly — refactor shared logic into regular helper functions
• For BEFORE ROW triggers: always RETURN NEW (or RETURN OLD for delete scenarios)
• For AFTER ROW triggers: use RETURN NULL
• For FOR EACH STATEMENT triggers: always RETURN NULL

---

Prevention Tips

Audit existing triggers regularly using the system catalog to catch misconfigured trigger functions before they cause issues in production:

-- Find all trigger functions and verify their return types
SELECT
    t.tgname       AS trigger_name,
    c.relname      AS table_name,
    p.proname      AS function_name,
    pg_get_function_result(p.oid) AS return_type
FROM pg_trigger t
JOIN pg_class c  ON t.tgrelid = c.oid
JOIN pg_proc p   ON t.tgfoid = p.oid
WHERE NOT t.tgisinternal
ORDER BY c.relname;

Use a standard trigger function template in your team and enforce code reviews for any trigger-related changes. A simple checklist — correct RETURNS trigger, explicit RETURN statement, no direct calls — prevents the majority of 39P01 errors before they reach production.

---

Related Errors

• 42809 — raised when a non-trigger function is assigned to a trigger during CREATE TRIGGER
• P0001 — raise_exception, commonly used inside trigger functions for validation
• 39004 — null_value_not_allowed, can occur alongside trigger protocol issues in strict PL/pgSQL functions

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.