Triggers

A trigger is a block of SQL logic that the database executes automatically in response to an INSERT, UPDATE, or DELETE on a table — without the application ever explicitly calling it. Where a stored procedure runs only when something invokes it with CALL, a trigger runs implicitly, as a side effect of a data-changing statement, which makes it well suited for enforcing rules or bookkeeping that should never be skippable, no matter which application or script performs the change.

Timing: BEFORE vs AFTER

A trigger is defined to fire at a specific timing relative to the statement that caused it:

Timing	When it runs	Typical use
BEFORE	Before the row is written	Validate or modify values before they are saved (e.g. normalize a column, block an invalid change)
AFTER	After the row has been written	React to a change that has already happened (e.g. write an audit log, update a related table)

Row-level vs statement-level triggers

Scope	Fires	Example
Row-level	Once per affected row	An UPDATE that changes 500 rows fires the trigger 500 times, once per row
Statement-level	Once per statement, regardless of row count	The same 500-row UPDATE fires the trigger exactly once, for the statement as a whole

Worked example: auto-updating an updated_at column

One of the most common trigger use cases is automatically stamping a row with the current time whenever it changes, so application code never has to remember to set it manually:

PostgreSQL: a BEFORE UPDATE row-level trigger

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(200),
  price      NUMERIC(10, 2),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- No matter what changes, updated_at is stamped automatically
UPDATE products SET price = 29.99 WHERE product_id = 1;

Worked example: auditing changes

An AFTER trigger is a natural fit for recording history without the application having to remember to log it:

PostgreSQL: an AFTER UPDATE trigger writing an audit row

SQL

CREATE TABLE price_audit (
  audit_id   SERIAL PRIMARY KEY,
  product_id INT,
  old_price  NUMERIC(10, 2),
  new_price  NUMERIC(10, 2),
  changed_at TIMESTAMP DEFAULT NOW()
);

CREATE FUNCTION log_price_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.price IS DISTINCT FROM NEW.price THEN
    INSERT INTO price_audit (product_id, old_price, new_price)
    VALUES (OLD.product_id, OLD.price, NEW.price);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_price_audit
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

Triggers can make behavior harder to trace

Because a trigger fires implicitly rather than being called explicitly, it is easy for a developer reading application code to have no idea that a simple UPDATE also writes an audit row, recalculates a total, or blocks the statement entirely. Debugging "why did this value change" or "why did this update fail" can mean checking application code, then discovering the real answer lives in a trigger definition inside the database. Use triggers for genuinely cross-cutting concerns (auditing, invariant enforcement) and document them clearly — avoid using them to implement core business logic that would be easier to find and test as ordinary application code or a stored procedure.

Syntax varies by dialect

The general concept of triggers is standard, but the exact syntax for CREATE TRIGGER, the mechanism for referencing OLD and NEW row values, and how the triggered logic is written (PL/pgSQL, T-SQL, PL/SQL) all differ across PostgreSQL, SQL Server, Oracle, and MySQL. Check the target database's documentation before writing a trigger.

A trigger runs automatically in response to INSERT, UPDATE, or DELETE, without being called explicitly.
BEFORE triggers can validate or modify a row before it is saved; AFTER triggers react to a change that already happened.
Row-level triggers fire once per affected row; statement-level triggers fire once per statement.
Common uses are auditing and automatically maintaining columns like updated_at.
Triggers add implicit behavior that can make debugging harder — use them deliberately and document them well.