PostgreSQL 27000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 27000: triggered data change violation

PostgreSQL error code 27000, triggered data change violation, occurs when a trigger attempts to perform a data modification that is not permitted within its execution context. This most commonly happens when an AFTER trigger or a FOR EACH STATEMENT trigger tries to directly modify the same table it is attached to. PostgreSQL enforces this restriction to protect data integrity and prevent infinite loops or unpredictable behavior.

---

Top 3 Causes

1. Modifying the Trigger's Own Table Inside an AFTER Trigger

The most common cause is attempting to run an UPDATE, INSERT, or DELETE on the triggering table itself from within an AFTER trigger.

Problematic code:

-- This AFTER trigger causes error 27000
CREATE OR REPLACE FUNCTION bad_after_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Trying to UPDATE the same table inside an AFTER trigger → ERROR 27000
    UPDATE employees
    SET updated_at = NOW()
    WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_bad_after
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION bad_after_trigger();

Fix — use a BEFORE trigger instead:

CREATE OR REPLACE FUNCTION good_before_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Modify NEW directly in a BEFORE trigger — no error
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_good_before
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION good_before_trigger();

---

2. Modifying the Target Table Inside a STATEMENT-Level Trigger

A FOR EACH STATEMENT trigger that attempts to modify the table it is fired on will also raise this error.

Problematic code:

CREATE OR REPLACE FUNCTION bad_stmt_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Modifying the same table in a STATEMENT-level trigger → ERROR 27000
    UPDATE employees SET last_bulk_update = NOW();
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_bad_stmt
AFTER UPDATE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION bad_stmt_trigger();

Fix — write to a separate audit table:

CREATE TABLE IF NOT EXISTS employees_audit (
    id          SERIAL PRIMARY KEY,
    operation   TEXT,
    occurred_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION good_stmt_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Write to a DIFFERENT table — no error
    INSERT INTO employees_audit (operation) VALUES (TG_OP);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_good_stmt
AFTER UPDATE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION good_stmt_trigger();

---

3. Indirectly Modifying the Target Table via a Called Function

Even if the trigger function itself does not contain a direct DML statement, calling another function or procedure that internally modifies the triggering table will still raise error 27000.

Fix — use a staging table to defer the change:

CREATE TABLE employees_pending (
    employee_id INTEGER,
    new_status  TEXT,
    processed   BOOLEAN DEFAULT FALSE
);

CREATE OR REPLACE FUNCTION safe_indirect_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Queue the change instead of applying it directly
    INSERT INTO employees_pending (employee_id, new_status)
    VALUES (NEW.id, NEW.status);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_safe_indirect
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION safe_indirect_trigger();

-- Process the queue outside the trigger context (e.g., via a scheduled job)
UPDATE employees e
SET status = p.new_status
FROM employees_pending p
WHERE e.id = p.employee_id AND p.processed = FALSE;

UPDATE employees_pending SET processed = TRUE WHERE processed = FALSE;

---

Quick Prevention Tips

• Always prefer BEFORE FOR EACH ROW triggers when you need to modify columns on the same table. Mutate the NEW record directly and return it — this is the safest and most efficient pattern.
• Audit and log to separate tables. Never design a trigger that writes back to its own table for logging purposes. Maintain a dedicated audit schema to keep trigger logic clean and avoid circular modification errors.

-- Useful query: inspect all triggers in your database before deployment
SELECT
    trigger_name,
    event_object_table,
    action_timing,
    event_manipulation,
    action_orientation
FROM information_schema.triggers
WHERE trigger_schema = 'public'
ORDER BY event_object_table;

---

Related Errors

• 09000 triggered_action_exception — A broader trigger execution error; often seen alongside 27000 when trigger logic is fundamentally flawed.
• 25006 read_only_sql_transaction — Raised when data modification is attempted inside a read-only transaction, conceptually similar to 27000 in that both block unauthorized data changes.

---

📖 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.