Learn to automate data integrity and auditing in PostgreSQL with triggers. You will write trigger functions in PL/pgSQL, choose BEFORE/AFTER and ROW/STATEMENT timing correctly, use NEW and OLD to validate and transform data, and build a reusable audit-log trigger — while avoiding recursion, ordering and performance pitfalls.
Watch the free preview
What a trigger is and how to write a trigger function — free to watch, no account needed.
What you'll learn
- Automate data integrity and auditing with PostgreSQL triggers
- Write PL/pgSQL trigger functions that use NEW and OLD correctly
- Choose BEFORE/AFTER and ROW/STATEMENT timing for each task
- Build a reusable audit-log trigger that records every change
- Diagnose and avoid trigger recursion, ordering and performance pitfalls
Syllabus
Trigger Fundamentals
What a trigger is and how to write a trigger functionFree preview
BEFORE and AFTER, and the NEW and OLD records
Row versus Statement, and Enforcing Rules
ROW-level versus STATEMENT-level triggers
Enforcing rules and transforming data with NEW and OLD
Auditing and Cascading Effects
Building an audit-log trigger
Cascading effects — triggers that change other tables
Pitfalls and Performance
Trigger pitfalls — recursion, ordering and surprises
Performance and when not to use a trigger
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10