Problem Statement
Compare using constraints versus triggers to enforce business rules. When is each appropriate?
Explanation
Use declarative constraints first. They are simple, automatic, and optimized by the engine. Examples include NOT NULL, UNIQUE, CHECK, and FOREIGN KEY. They are easy to reason about and are validated by the optimizer.
Triggers are for rules that require procedural logic across rows or tables, audit trails, or derived writes. They add complexity and can surprise callers if not documented. Prefer constraints for static invariants; use triggers sparingly for cross-row logic you cannot express declaratively.
Code Solution
SolutionRead Only
ALTER TABLE invoices ADD CONSTRAINT chk_amt CHECK (total >= 0); -- Trigger only if you need side-effects like audit rows
Practice Sets
This question appears in the following practice sets:
