CockroachDB 26.2 makes triggers an operational compatibility feature

Replacing a trigger function used to require dropping and recreating the triggers that called it. CockroachDB 26.2 removes that restriction and atomically updates dependent triggers to use the replacement body. This is one of the practical changes accompanying generally available SQL triggers in the April 27 release. Trigger support now includes more of the lifecycle operations needed to maintain database logic after its first deployment.

The release describes PostgreSQL-compatible BEFORE and AFTER triggers for insert, update and delete operations. That can reduce migration work for an application whose behavior already lives in trigger functions. Compatibility still has a defined scope. Some trigger forms remain unsupported, and existing CockroachDB functions can change behavior because TG_ARGV now uses PostgreSQL's zero-based indexing.

I value CockroachDB's serializable default and the adoption advantages of speaking the PostgreSQL wire protocol. Trigger compatibility extends the latter beyond connecting a familiar client. It concerns behavior an application may already depend on inside the database. The relevant question is whether that behavior can be created and maintained with the same intended result, including the less frequent operations used during schema changes.

The trigger runs once for each affected row

The trigger documentation defines a row-level model. FOR EACH ROW is required, and the function runs for each affected row when the selected event occurs. A WHEN condition can restrict activation further. This gives a trigger a specific relationship to data changes, rather than making it an arbitrary function invoked once for the whole statement.

Trigger functions are written in PL/pgSQL, declare no function arguments and return TRIGGER. Arguments attached to the trigger definition are constant strings exposed through trigger variables. The function must exist before the trigger is created. Those rules make the function and its table binding separate schema objects, which explains why changing or replacing one has consequences for the other.

OLD and NEW provide the relevant row versions. Insert triggers have a new row, delete triggers have an old row, and update triggers can use both. The documentation requires parentheses when accessing their fields, as in (OLD).is_locked. A migration that copies unparenthesized field access from another environment therefore needs more than a matching function name.

For a BEFORE trigger, returning NEW allows an insert or update to proceed with that row, while returning NULL stops the operation for the row. The return value of an AFTER trigger is ignored, and returning NULL is the documented convention. These differences make the function's return path part of its behavior. A function that computes the right intermediate value but returns the wrong row can still implement the wrong policy.

A small rejection rule shows the execution contract

The CREATE TRIGGER reference demonstrates preventing deletion of a locked row. The following shortened version preserves the documented function structure and field-access syntax.

CREATE TABLE records (
    id INT PRIMARY KEY,
    is_locked BOOL NOT NULL DEFAULT false
);

CREATE FUNCTION prevent_locked_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF (OLD).is_locked THEN
        RAISE EXCEPTION 'Record is locked';
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE PLpgSQL;

CREATE TRIGGER protect_locked_record
BEFORE DELETE ON records
FOR EACH ROW
EXECUTE FUNCTION prevent_locked_delete();

For an unlocked row, returning OLD allows the delete to proceed. For a locked row, the exception rejects the operation. A useful test checks the error and verifies that the row remains, as the reference's example does. Merely proving that the function and trigger definitions are accepted would leave their effect untested.

The example also shows a design choice. The rule applies at the table's write path, so clients issuing deletes encounter it through the database. That can be useful when several applications modify the same table. It also means a reader of application code needs to inspect database definitions to understand why a delete is rejected. The trigger is additional executable behavior associated with the table.

Replacement and dependencies make the feature maintainable

Version 26.2 supports CREATE OR REPLACE TRIGGER. If the named trigger already exists on the table, the new definition replaces it. Otherwise, the command creates it. This avoids making deployment code choose a separate create or replace path merely because the binding already exists.

Function replacement is a separate improvement. CREATE OR REPLACE FUNCTION now works for a trigger function with active dependent triggers, atomically updating them to use the new body. Previously, CockroachDB rejected this operation as unimplemented and required dropping and recreating the triggers. That old restriction turned a function-body change into a larger schema procedure.

Dependencies receive attention during removal too. DROP COLUMN and DROP INDEX with CASCADE now remove dependent triggers instead of failing with an unimplemented error. The release also accepts DROP TRIGGER ... CASCADE for PostgreSQL compatibility. Since CockroachDB triggers cannot themselves have dependents, that option behaves like RESTRICT or omission for that command.

These are distinct meanings of cascading removal. A trigger can depend on a column or index, while the trigger itself has no dependents under the supported model. Keeping that direction clear prevents a compatibility claim from implying a dependency graph the implementation does not provide. The release notes explain the behavior of each command separately.

Introspection gains pg_get_triggerdef, which returns the trigger's creation statement for a given OID. The new pg_trigger_depth() reports nesting depth, returning zero outside a trigger. These additions help tools inspect and reason about trigger-based logic without relying entirely on separately maintained copies of its definitions.

TG_ARGV changes an existing function's meaning

The TG_ARGV change is backward incompatible. Previously, TG_ARGV[1] returned the first trigger argument. In 26.2, TG_ARGV[0] returns the first and index one returns the second. The release explicitly ties this to PostgreSQL compatibility and removes the requirement to enable allow_create_trigger_function_with_argv_references before using the variable.

An existing function can remain syntactically valid while selecting another argument after the change. That makes argument indexing a semantic migration issue. Tests need to distinguish the argument values, because identical test arguments would conceal an off-by-one access. This is a direct consequence of the documented index change, without implying that the server rewrites every existing function body automatically.

Multiple triggers also have ordering rules. The documentation says before triggers precede after triggers and that names determine alphabetical order among triggers. The output row of one before trigger passes to the next. Renaming a trigger can therefore affect behavior when successive functions transform or validate the same row. Naming is part of the execution order in this model.

This is where my preference for explicit consistency meets a practical concern about readability. A trigger can keep a rule near the data, but several interacting functions make the full write behavior less visible at a single call site. The documented ordering and inspectable definitions are necessary information for understanding that behavior, especially when replacing a function shared by several bindings.

General availability retains compatibility limits

Statement-level triggers and INSTEAD OF triggers remain unsupported in the versioned documentation. So do REFERENCING, update triggers with a column list, and statement-level truncate triggers. Hidden columns are not visible to triggers. An existing PostgreSQL application using one of those forms still has a migration problem despite the generally available label.

Creating a trigger requires TRIGGER privilege on its table and EXECUTE on its function. The reference notes that the public role has function execution privilege by default unless it has been revoked. Those requirements belong in deployment-role design, since the user installing a trigger may have different privileges from an application user invoking writes against the table.

The release lists trigger disabling and enabling among features requiring upgrade finalization. It supports named triggers and the ALL or USER forms after that point. This timing qualification matters for deployment scripts run during a mixed-version upgrade. The desired maintenance command and its availability are separate from whether ordinary trigger execution has been adopted by the application.

View authorization changes elsewhere in 26.2 reinforce the need to test database-resident behavior under the intended identity. After finalization, a view checks its owner's privileges on underlying tables, and the owner's row-security policies apply. The release also adds the PostgreSQL-compatible security_invoker option to request underlying checks as the querying user. These rules concern views, not trigger privilege semantics, but both can affect an application whose behavior depends on stored database definitions. Sharing familiar SQL syntax does not remove the need to identify which identity and object definition an operation actually uses.

For a migration based on supported row triggers, 26.2 therefore improves both compatibility and maintenance. The concrete gains include replacing a function without rebuilding its bindings and inspecting the resulting definition through SQL. Existing argument references still need to match the new indexing, and unsupported trigger forms remain specific items in the application's compatibility assessment.