PostgreSQL Triggered Data Change Violation (SQLSTATE 27000)

When PostgreSQL raises ERROR: triggered data change violation with SQLSTATE 27000, you are seeing the condition name triggered_data_change_violation. This error occurs when a trigger function attempts to perform a data modification that is explicitly prohibited for that trigger context — most commonly, when an AFTER trigger on a view tries to modify the view's underlying base table in a way that conflicts with the trigger-based rules governing that view.

What This Error Means

SQLSTATE 27000 belongs to class 27 — Triggered Data Change Violation. This is a narrow, specialized class with only one defined condition: triggered_data_change_violation itself. It is distinct from constraint violations (class 23) or transaction integrity errors (class 25).

The error is most frequently encountered in the context of updatable views using INSTEAD OF triggers, or when WITH CHECK OPTION is defined on a view and a trigger-driven row modification produces a result that falls outside the view's qualifying condition. PostgreSQL evaluates the check option after the trigger fires, and if the modified row would no longer be visible through the view, it raises SQLSTATE 27000 rather than silently allowing the invisible-row write.

After this error is raised, the current statement is aborted and the transaction is left in an aborted state. You must issue a ROLLBACK (or ROLLBACK TO SAVEPOINT) before issuing any further commands in the same connection. The error does not by itself terminate the database session.

Common Causes

1. WITH CHECK OPTION on a view combined with a trigger that produces out-of-scope rows. If a view is created with WITH CHECK OPTION (or WITH CASCADED CHECK OPTION) and an INSTEAD OF trigger modifies the row such that the resulting row no longer satisfies the view's WHERE clause, PostgreSQL rejects the write with SQLSTATE 27000.

2. Nested trigger chains that violate the originating view's check constraint. A trigger on a base table fires in response to a view-level INSTEAD OF trigger, and the cascaded write produces a row that violates the view's check option when control returns to the view layer.

3. Incorrect INSTEAD OF trigger logic that modifies columns used in the view filter. If the trigger body updates a column that appears in the view's defining WHERE clause, the post-write check can fail, triggering SQLSTATE 27000.

How to Fix triggered_data_change_violation

1. Review and align the trigger body with the view's WITH CHECK OPTION predicate. Ensure that any row the INSTEAD OF trigger writes to the base table would still satisfy the view's WHERE clause. For example:

-- View with check option
CREATE VIEW active_users AS
  SELECT id, name, status
  FROM users
  WHERE status = 'active'
  WITH CHECK OPTION;

-- INSTEAD OF trigger must not set status to anything other than 'active'
CREATE OR REPLACE FUNCTION trg_active_users_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Ensure we don't violate the view's WHERE status = 'active' filter
  INSERT INTO users (id, name, status)
  VALUES (NEW.id, NEW.name, 'active');  -- hard-code or validate status
  RETURN NEW;
END;
$$;

2. Remove WITH CHECK OPTION if the trigger is intentionally writing rows outside the view's scope. If the design requires inserting rows that are not visible through the view (e.g., creating inactive users via an active-users view trigger), remove the check option:

CREATE OR REPLACE VIEW active_users AS
  SELECT id, name, status
  FROM users
  WHERE status = 'active';
-- No WITH CHECK OPTION

3. Use LOCAL vs CASCADED check option deliberately. WITH LOCAL CHECK OPTION only checks the immediate view's predicate, while WITH CASCADED CHECK OPTION (the default) checks all underlying views in the chain. If cascaded checking is too strict, switch to LOCAL:

CREATE VIEW active_users AS
  SELECT id, name, status
  FROM users
  WHERE status = 'active'
  WITH LOCAL CHECK OPTION;

4. Trace the trigger chain when nested triggers are involved. Use SET log_min_messages = DEBUG1; temporarily or add RAISE NOTICE statements to your trigger functions to observe which trigger fires, what row values it produces, and where the check option evaluation fails.

CREATE OR REPLACE FUNCTION trg_debug()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'trigger fired: NEW = %', NEW;
  -- rest of trigger logic
  RETURN NEW;
END;
$$;

Additional Information

• SQLSTATE class 27 (Triggered Data Change Violation) has existed in the SQL standard and PostgreSQL for many years; no specific PostgreSQL version introduced or significantly altered its behavior.
• Related SQLSTATE codes in adjacent classes: 25006 (READ_ONLY_SQL_TRANSACTION) if you are attempting writes in a read-only context, and class 23 constraint violation codes if the underlying table constraint is what rejects the row.
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a generic database error with the SQLSTATE 27000 in the exception's pgcode or getSQLState() attribute. ORMs such as SQLAlchemy will raise it as a generic DatabaseError or OperationalError depending on the driver; inspect the wrapped exception's orig.pgcode to confirm SQLSTATE 27000.
• This error is uncommon in applications that avoid WITH CHECK OPTION on trigger-managed views. If you are seeing it frequently, it usually indicates a mismatch between the view definition and the trigger logic that was introduced during a schema change.

Frequently Asked Questions

What is the difference between SQLSTATE 27000 and a check constraint violation (SQLSTATE 23514)? A check constraint violation (check_violation, SQLSTATE 23514) is raised when a row fails a CHECK constraint defined directly on a table or column. SQLSTATE 27000 is raised specifically when a view's WITH CHECK OPTION rejects a row that a trigger would have written — it is a higher-level, view-layer check, not a table-level constraint.

Does this error always roll back the entire transaction? No. Like most statement-level errors in PostgreSQL, SQLSTATE 27000 aborts only the current statement and leaves the transaction in an error state. If you are using savepoints, you can roll back to a savepoint and continue the transaction. Without a savepoint, you must ROLLBACK the entire transaction before sending new commands on that connection.

Can this error occur without WITH CHECK OPTION? In standard PostgreSQL usage, WITH CHECK OPTION is the primary mechanism that raises SQLSTATE 27000. Without it, trigger-based view modifications generally succeed (assuming no table-level constraint is violated). If you are seeing SQLSTATE 27000 without an explicit WITH CHECK OPTION, check whether the view inherits a check option from an underlying view via WITH CASCADED CHECK OPTION.

How do I find which view's check option is being violated? Check the full error message — PostgreSQL typically includes the view name in the detail text, for example: ERROR: new row violates check option for view "active_users". If the message is ambiguous, enable log_error_verbosity = verbose in postgresql.conf or use \set VERBOSITY verbose in psql to see additional context.