Database trigger
A database hook that runs automatically when rows are inserted, updated, or deleted, even if a different app made the change.
See it
What it is
A database trigger tells the database to run a function automatically when an INSERT, UPDATE, DELETE, or other supported event happens. It can run before or after the statement, and once per changed row or once for the whole statement. In PostgreSQL, OLD and NEW expose the row before and after a row-level change.
Reach for one when a rule must hold no matter which application writes the table. Audit rows, derived timestamps, and a transactional outbox are common cases. Keep the action small and local so an innocent UPDATE does not secretly launch a maze of extra work.
Gotcha: triggers hide control flow. They can recurse, multiply write cost, surprise bulk imports, and make a failure appear far from the statement that caused it. Name them clearly, test multi-row statements rather than only single-row examples, and never assume their firing order unless the database documents it.
Ask AI for it
Create a PostgreSQL audit trigger with a PL/pgSQL trigger function and CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE. Record TG_OP, clock_timestamp(), current_user, the table name, and OLD and NEW row data as jsonb in an append-only audit table, while avoiding recursive trigger calls with pg_trigger_depth(). Include forward and rollback migrations plus tests for single-row and multi-row INSERT, UPDATE, and DELETE statements.