Lesson 42 • Advanced
Triggers vs Application Logic
Learn when to use database triggers and when to keep logic in your application code.
✅ What You'll Learn
- • Building audit trails with triggers
- • Complex validation triggers with business rules
- • When to move logic to your application instead
- • Common trigger pitfalls: recursion, performance, hidden deps
🤔 Triggers or App Code?
🎯 Real-World Analogy: Think of triggers like automatic sprinklers — they react to fire without human intervention. Great for fire safety (audit logs), but you wouldn't use sprinklers to cook dinner (complex business logic). Some things need a chef (your application).
✅ Use Triggers For
- • Audit logs (who changed what)
- • Data validation (enforce invariants)
- • Derived values (updated_at timestamps)
- • Denormalisation sync (update cached counts)
❌ Use App Code For
- • Sending emails / notifications
- • Calling external APIs
- • Complex business workflows
- • Anything slow or async
Try It: Audit Trail Trigger
Build a generic audit trigger that logs all changes to any table
-- Audit trigger: Track every change automatically
-- Perfect use case for triggers — guaranteed to fire
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
operation TEXT NOT NULL,
old_data JSONB,
new_data JSONB,
changed_by TEXT DEFAULT current_user,
changed_at TIMESTAMPTZ DEFAULT NOW()
);
-- Generic audit function
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit
...Try It: Validation Trigger
Enforce credit limits and business rules with BEFORE INSERT triggers
-- Validation trigger: Enforce complex business rules
-- Use when CHECK constraints aren't powerful enough
CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
DECLARE
v_credit_limit DECIMAL;
v_outstanding DECIMAL;
BEGIN
-- Rule 1: Order total must be positive
IF NEW.total <= 0 THEN
RAISE EXCEPTION 'Order total must be positive, got %', NEW.total;
END IF;
-- Rule 2: Check customer credit limit
SELECT credit_limit INTO v_credit_limit
FROM cus
...Try It: Application Logic Alternatives
Use generated columns and views instead of triggers for calculations
-- When to use APPLICATION LOGIC instead of triggers
-- These are better handled in your app code:
-- ❌ BAD: Sending emails from a trigger
-- Triggers run inside transactions — if the email
-- service is slow, your INSERT hangs!
-- CREATE TRIGGER send_welcome_email ... -- DON'T DO THIS
-- ✅ GOOD: Use the app layer for side effects
-- pseudocode:
-- BEGIN TRANSACTION
-- INSERT INTO users (...) VALUES (...);
-- COMMIT;
-- queue_email(user.email, 'Welcome!'); -- async, outside transaction
--
...Try It: Trigger Pitfalls
Avoid recursion, bulk performance issues, and hidden dependencies
-- Common trigger pitfalls and how to avoid them
-- PITFALL 1: Recursive triggers (trigger fires itself)
-- Order update → trigger updates order → trigger fires again → infinite loop!
-- Fix: Use a guard condition
CREATE OR REPLACE FUNCTION safe_update_func()
RETURNS TRIGGER AS $$
BEGIN
-- Prevent recursion: only act if relevant columns changed
IF NEW.status IS DISTINCT FROM OLD.status THEN
NEW.updated_at = NOW();
-- Only update related rows, not the same table
...📋 Quick Reference
| Scenario | Use | Why |
|---|---|---|
| Audit logging | Trigger | Can't be bypassed by app bugs |
| Email notifications | App code | Async, external dependency |
| updated_at timestamp | Trigger | Simple, always consistent |
| Pricing calculation | App code | Complex, needs testing |
| Referential integrity | Constraints | Built-in, fastest option |
🎉 Lesson Complete!
You now know when triggers are the right tool and when app code is better. Next, learn about schema versioning and database CI/CD!
Sign up for free to track which lessons you've completed and get learning reminders.