Atlas now manages CockroachDB triggers, functions, and procedures as first-class resources - inspected, diffed, and applied alongside tables and views.

CockroachDB users can now manage triggers, functions, and procedures with Atlas. They are written exactly like their PostgreSQL counterparts, and Atlas takes care of the places where CockroachDB's SQL surface differs.

Triggers

A trigger is declared with the table it fires on, the events it reacts to, and the function it executes:

schema.hcl

function "audit_order" {
  schema = schema.public
  lang   = PLpgSQL
  return = trigger
  as     = <<-SQL
  BEGIN
  RETURN new;
  END;
  SQL
}
trigger "audit_orders" {
  on = table.orders
  before {
    insert = true
  }
  for  = ROW
  when = "((new).amount > 100)"
  execute {
    function = function.audit_order
    args     = ["orders"]
  }
}

Atlas plans:

atlas schema apply

-- create "audit_order" function:
CREATE FUNCTION "public"."audit_order" () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN new;
END;
$$;
-- create trigger "audit_orders":
CREATE TRIGGER "audit_orders" BEFORE INSERT ON "public"."orders" FOR EACH ROW WHEN ((new).amount > 100) EXECUTE FUNCTION "public"."audit_order"('orders');

Changing a trigger uses CREATE OR REPLACE TRIGGER from v26.2:

CockroachDB v26.2 and above

-- modify "audit_orders" trigger:
CREATE OR REPLACE TRIGGER "audit_orders" BEFORE INSERT ON "public"."orders" FOR EACH ROW WHEN ((new).amount > 500) EXECUTE FUNCTION "public"."audit_order"('orders');

On earlier versions, Atlas drops and recreates it instead:

CockroachDB v25.3 to v26.1

-- drop "audit_orders" trigger:
DROP TRIGGER "audit_orders" ON "public"."orders";
-- create trigger "audit_orders":
CREATE TRIGGER "audit_orders" BEFORE INSERT ON "public"."orders" FOR EACH ROW WHEN ((new).amount > 500) EXECUTE FUNCTION "public"."audit_order"('orders');

Trigger inspection requires CockroachDB v25.3 or later. Before that, pg_trigger is empty and Atlas cannot read triggers back.

Functions and Procedures

Functions and procedures are inspected with their arguments, language, body, and security mode:

atlas schema inspect

function "order_fee" {
  schema = schema.public
  lang   = SQL
  arg "amount" {
    type = numeric
  }
  return   = numeric
  as       = "SELECT amount * 0.03;"
  security = DEFINER
}
procedure "archive_orders" {
  schema     = schema.public
  lang       = SQL
  as         = "DELETE FROM defaultdb.public.orders WHERE amount = 0;"
  security   = DEFINER
  depends_on = [table.orders]
}

Switching both to SECURITY INVOKER shows two of CockroachDB's quirks at once. It implements ALTER FUNCTION ... SECURITY but not the procedure equivalent, so Atlas replaces the procedure - and writes the clause explicitly, since older versions keep the previous value when a routine is replaced without one:

atlas schema apply

-- modify "order_fee" function attribute:
ALTER FUNCTION "public"."order_fee" SECURITY INVOKER;
-- modify "archive_orders" procedure:
CREATE OR REPLACE PROCEDURE "public"."archive_orders" () LANGUAGE sql SECURITY INVOKER AS $$ DELETE FROM defaultdb.public.orders WHERE amount = 0; $$;

Argument defaults are only readable from v26.2. On earlier versions CockroachDB does not report them, so they are not part of the inspected state.

Dependency Ordering

CockroachDB rejects DROP FUNCTION ... CASCADE, so a function cannot be dropped while a trigger still executes it. Atlas orders the statements so the database accepts them. Pointing the trigger at a new function drops the trigger before the function it used to execute:

atlas schema apply

-- create "audit_order_v2" function:
CREATE FUNCTION "public"."audit_order_v2" () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN new;
END;
$$;
-- drop "audit_orders" trigger:
DROP TRIGGER "audit_orders" ON "public"."orders";
-- create trigger "audit_orders":
CREATE TRIGGER "audit_orders" BEFORE INSERT ON "public"."orders" FOR EACH ROW WHEN ((new).amount > 500) EXECUTE FUNCTION "public"."audit_order_v2"('orders');
-- drop "audit_order" function:
DROP FUNCTION "public"."audit_order";

Getting Started

Inspect an existing cluster to see your triggers, functions, and procedures as code:

atlas schema inspect -u "crdb://user:pass@localhost:26257/defaultdb?sslmode=disable"

The CockroachDB guide walks through the full setup, from connecting to planning and applying migrations.

CockroachDB: Triggers, Functions, and Procedures

Triggers

Functions and Procedures

Dependency Ordering

Getting Started