Postgres Trigger Generator

Build a correct PostgreSQL trigger without re-reading the docs. Pick the table, timing (BEFORE, AFTER or INSTEAD OF), the events (INSERT, UPDATE with an optional column list, DELETE, TRUNCATE) and row or statement level, then choose a battle-tested function template: touch an updated_at column, write a JSONB audit history, block the operation entirely, or start from a TG_OP skeleton. The generator emits the plpgsql trigger function, the CREATE OR REPLACE TRIGGER statement with an optional WHEN condition, plus inspection, disable and rollback SQL — and it warns you when a combination is invalid, like FOR EACH ROW on TRUNCATE. Runs entirely in your browser.

TableTimingLevel

Events:INSERTUPDATEDELETETRUNCATEUPDATE OF columns

Function templateFunction nameWHEN condition

Trigger function (plpgsql)

CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER

CREATE OR REPLACE TRIGGER trg_orders_touch
BEFORE UPDATE
ON public.orders
FOR EACH ROW
EXECUTE FUNCTION touch_updated_at();

Inspect / disable / rollback

-- Inspect and roll back:
-- List triggers on the table
SELECT tgname, pg_get_triggerdef(oid) FROM pg_trigger
WHERE  tgrelid = 'public.orders'::regclass AND NOT tgisinternal;

-- Remove the trigger (the function stays and can serve other tables)
DROP TRIGGER IF EXISTS trg_orders_touch ON public.orders;
-- DROP FUNCTION IF EXISTS touch_updated_at();

-- Temporarily disable / re-enable instead of dropping
ALTER TABLE public.orders DISABLE TRIGGER trg_orders_touch;
ALTER TABLE public.orders ENABLE TRIGGER trg_orders_touch;

Notes

• CREATE OR REPLACE TRIGGER needs PostgreSQL 14+. On older versions, DROP TRIGGER IF EXISTS first, then CREATE TRIGGER.
• BEFORE ROW triggers returning NULL silently skip the operation for that row — a common source of 'my insert disappeared' bugs.

Do more than postgres trigger generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table, pick the timing (BEFORE / AFTER / INSTEAD OF), tick the events, and choose row-level or statement-level execution.
Choose a function template — auto-update updated_at, JSONB audit log, block the operation, or a custom TG_OP skeleton — and optionally add a WHEN condition.
Copy the generated trigger function and CREATE TRIGGER statement into your database, and keep the rollback block for later.

Frequently asked questions

How do I create a trigger in PostgreSQL?

Two steps: first create a function that RETURNS trigger, then attach it with CREATE TRIGGER. For example: CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END; $$; then CREATE TRIGGER trg_orders_touch BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION touch_updated_at(); Inside the function, NEW holds the incoming row, OLD holds the stored row, and TG_OP tells you which operation fired the trigger.

What is the difference between BEFORE and AFTER triggers in Postgres?

A BEFORE ROW trigger runs before the row is written, so it can modify NEW (for example set updated_at) or return NULL to skip the operation for that row. An AFTER trigger runs once the row change is complete; its return value is ignored, which makes it the right place for side effects like audit logging or queueing work, because it only sees changes that passed all constraints. INSTEAD OF triggers are a third kind that only apply to views and replace the operation entirely.

Do PostgreSQL triggers slow down writes?

Every row-level trigger adds a function call per affected row inside the same transaction, so a heavy trigger on a hot table does slow writes — an audit trigger that writes a JSONB copy roughly doubles the write work. Keep trigger bodies small, prefer statement-level triggers with transition tables for bulk operations, and add a WHEN condition so the function is only called when relevant columns actually change. To inspect triggers, test them and profile query times against a live database, you can use Chat2DB — a free AI database client: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.