Triggers

Triggers automatically run database functions when table rows are inserted, updated, or deleted. They are useful for maintaining update times, writing audit logs, and synchronizing summary fields close to the data layer.

Use cases

• Maintain updated_at automatically.
• Write audit logs or change history.
• Maintain counters, statuses, or denormalized fields.
• Run database-level validation for complex writes.

Do not put slow external calls in triggers. Triggers run inside transactions, so slow logic affects write performance and lock release.

Maintain updated time

Trigger functions read NEW / OLD row variables and return trigger. This is procedural logic and must use LANGUAGE plpgsql.

You can create triggers in the console or with SQL statements. The CloudBase PostgreSQL management UI is currently consistent with the Supabase console, so it is suitable for configuring common table triggers.

Console

1. Make sure the target table contains the column to maintain automatically, such as public.todos.updated_at.
2. In the Database Functions management page, create or select a trigger function such as public.set_updated_at. The function return type must be trigger, and the language must be LANGUAGE plpgsql.
3. Open the Triggers management page.
4. Click Create Trigger.
5. Select the target table public.todos.
6. Set the trigger timing to BEFORE, the event to UPDATE, and the orientation to FOR EACH ROW.
7. Select the trigger function public.set_updated_at, then confirm and submit.

The console is suitable for creating common triggers. For complex conditions, bulk migrations, or versioned releases together with functions, use SQL statements instead.

SQL

Create a trigger function first:

create or replace function public.set_updated_at()
returns trigger
LANGUAGE plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

Then bind it to a table:

create trigger todos_set_updated_at
before update on public.todos
for each row
execute function public.set_updated_at();

Write audit logs

create table public.audit_logs (
  id bigint generated by default as identity primary key,
  table_name text not null,
  row_id text not null,
  action text not null,
  changed_at timestamptz not null default now()
);

create or replace function public.log_todo_changes()
returns trigger
LANGUAGE plpgsql
as $$
begin
  insert into public.audit_logs(table_name, row_id, action)
  values ('todos', coalesce(new.id, old.id)::text, tg_op);
  return coalesce(new, old);
end;
$$;

create trigger todos_audit_log
after insert or update or delete on public.todos
for each row
execute function public.log_todo_changes();

Debug triggers

Trigger logic runs inside the database. Test it on staging tables first. Use raise notice for temporary debug output.

raise notice 'trigger %, row id %', tg_op, new.id;

Remove unnecessary debug output before launch to keep logs readable.

Notes

• Keep triggers small and avoid complex loops or external dependencies.
• Multiple triggers on the same table can affect each other. Name and test them clearly.
• Trigger exceptions roll back the whole write.
• Before large imports, evaluate the extra write cost caused by triggers.