Triggers

Running a function automatically on INSERT, UPDATE or DELETE: audit logs and business rules the database enforces itself.

A trigger calls a function automatically whenever rows change. It guarantees that something always happens — writing an audit record, maintaining a derived value, rejecting an invalid change — no matter which application or script changed the data.

An audit log and a guard rule

The trigger function returns trigger and can read OLD (the row before) and NEW (the row after).

AFTER UPDATE OF salary ... FOR EACH ROW: one audit row per changed employee, written in the same transaction as the update.

The BEFORE trigger runs before the row is written and can reject it with RAISE EXCEPTION, which aborts the whole statement.

CONTEXT shows which function and line raised the error.

Using triggers well

CREATE FUNCTION f() RETURNS trigger... BEGIN... END; -- no RETURN... RETURN NEW; END;

A BEFORE row trigger that returns NULL silently skips the operation for that row. Return NEW (or OLD for DELETE) to let it proceed.

What are OLD and NEW in a row trigger?

Records holding the row before the change (OLD, for UPDATE and DELETE) and after it (NEW, for INSERT and UPDATE).

When would you use a BEFORE trigger instead of AFTER?

To validate or modify the row before it is stored (setting default or derived values, rejecting invalid data). AFTER triggers suit side effects like audit logs that need the final row.