Why this decision matters
Modern systems often have multiple apps, scripts, and analysts hitting the same database. If only one of them validates that "order quantity must be positive," the next one will eventually break it. Putting the rule in the database itself means it's enforced no matter who's at the keyboard. Triggers and stored procedures take this further — they let the database actively respond to events, audit changes, and run multi-step logic atomically.
By the end of this topic you'll be able to
Add primary key, foreign key, UNIQUE, NOT NULL, and CHECK constraints; write a trigger that audits changes to a sensitive table; understand when to use a stored procedure vs. application code; reason about transaction control and ACID properties.
Materials
Key concepts to know
- PK / FK constraints — referential integrity, cascade options.
- UNIQUE, NOT NULL, CHECK — column-level rules.
- Triggers — code that fires automatically on INSERT, UPDATE, or DELETE.
- Stored procedures & functions — reusable SQL logic with parameters.
- Transactions — BEGIN, COMMIT, ROLLBACK. Atomic units of work.
- ACID — Atomicity, Consistency, Isolation, Durability. The database's promise.
Class notes & references
- Advanced Database Concepts — Class NotesConstraints, triggers, procedures, transactions.
- Application Security — Class NotesHow database-level security fits with app-level security.
Triggers — Prescription Audit walkthrough
Step-by-step trigger tutorial built around a prescription-audit use case (track every change to controlled-substance records).
- Trigger Tutorial — Prescription Audit (Step-by-step)
- w6_prescription_database.sqlSQL script to spin up the prescription schema with audit triggers.