When would you use a trigger vs application code vs a database constraint?

Use constraints for simple validation (NOT NULL, CHECK, FOREIGN KEY). Use triggers for automation that must happen every time (audit logging, denormalization). Use application code for business logic that changes frequently. Hierarchy: Constraints (simplest) → Triggers (automatic) → Application (flexible). Example: CHECK constraint for price > 0, trigger for audit logging, application code for email notifications.

What are the performance implications of triggers?

Triggers add overhead. Simple triggers: 5-10ms. Complex triggers: 50-100ms. This multiplies with volume. Optimization: Keep simple, use WHEN clause, use statement-level for bulk, avoid queries, monitor with EXPLAIN ANALYZE. Trade-off: Triggers ensure consistency but cost performance.

How do you debug a trigger that is causing issues?

Query trigger metadata, temporarily disable trigger, add logging, use RAISE NOTICE, check execution time with EXPLAIN ANALYZE, review audit logs, test in isolation. Key is making hidden behavior visible through logging and monitoring.

Triggers & Stored Procedures

SQL Practice Online