CS377: Database Design - SQL Triggers

Activity Goals

The goals of this activity are:

1. To explain how a trigger automates integrity constraints and audit logging inside the database
2. To write a CREATE TRIGGER statement that fires before or after an INSERT, UPDATE, or DELETE
3. To trace, row by row, what a trigger does to a database when a statement fires it

Supplemental Reading

Feel free to visit these resources for supplemental background reading material.

• SQLite CREATE TRIGGER Documentation
• SQL Triggers Slides
• The pubs Sample Database Schema

The Activity

Directions

Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity, and compare with your group to prepare for our whole-class discussion. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.

---

Model 1: Anatomy of a Trigger

An event/condition/action rule, and its SQL trigger equivalent

Plain language	Trigger clause
"Whenever a new sale is recorded..."	AFTER INSERT ON sales
"...if the book actually exists..."	WHEN (a condition on the new row)
"...add its quantity to that book's year-to-date total."	UPDATE titles SET ytd_sales = ...

Questions

1. A trigger is a rule stored inside the database: when some event happens (an INSERT, UPDATE, or DELETE on a table), the database automatically runs some SQL of yours. Why might it be safer to enforce a rule like "keep the year-to-date total up to date" inside the database, rather than trusting every application program to remember to do it?
2. Triggers can fire BEFORE or AFTER the triggering statement. Which would you use to reject a bad row, and which would you use to react to a row that was just saved? Why?
3. Inside the trigger body, NEW refers to the row being inserted or the new version of an updated row, and OLD refers to the row being deleted or the old version of an updated row. Which of NEW and OLD are available in an INSERT trigger? A DELETE trigger? An UPDATE trigger?

---

Model 2: A Trigger on the pubs Database

CREATE TRIGGER update_ytd_sales
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE titles
    SET ytd_sales = COALESCE(ytd_sales, 0) + NEW.qty
    WHERE title_id = NEW.title_id;
END;

Questions

1. In the pubs bookstore database, the sales table records each order (stor_id, ord_num, qty, title_id), and the titles table caches a running ytd_sales total for each book. Walk through what happens, step by step, when a store inserts a sale of 20 copies of title BU1032. Which table changes first? Which row of titles is touched, and why only that one?
2. What could go wrong if ytd_sales were instead updated by every cash-register application on its own? Which anomaly from our data integrity discussions does the trigger prevent?
3. Write a BEFORE INSERT trigger on sales that raises an error (in SQLite: SELECT RAISE(ABORT, 'quantity must be positive');) when NEW.qty <= 0. Test it with a bad insert: what does the database report, and does the bad row appear in the table?
4. Design an audit log: create a table price_history(title_id, old_price, new_price, changed_on) and write an AFTER UPDATE trigger on titles that records every price change. Why is a trigger a natural home for provenance data like this?

---

Why Triggers?

So far, when we wanted two pieces of data to stay consistent — a running total, a log of changes, a rule like “quantity must be positive” — we relied on every program that touches the database to remember to enforce it. That works right up until someone writes a new program (or opens an interactive SQL shell) and forgets. A trigger moves the rule into the database itself: it is a saved piece of SQL that the database runs automatically whenever a particular event happens on a particular table. No application can forget to run it, because no application runs it — the database does.

Triggers are the enforcement half of the data integrity story: constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK) declare simple rules, and triggers handle everything richer — cross-table bookkeeping, audit trails, and validations that need a little logic.

The Shape of a CREATE TRIGGER Statement

Every trigger answers three questions — when, on what event, and do what:

CREATE TRIGGER trigger_name        -- 1. a name, so you can DROP it later
BEFORE INSERT ON tablename         -- 2. when (BEFORE/AFTER) and which event (INSERT/UPDATE/DELETE) on which table
FOR EACH ROW                       -- 3. run once per affected row
BEGIN
    -- 4. the SQL to run; NEW.column and OLD.column refer to the row involved
END;

Clause	Choices	What it means
BEFORE / AFTER	before or after the row change is applied	BEFORE can inspect and veto the change; AFTER reacts to a change that already happened
INSERT / UPDATE / DELETE	the triggering event	which statement type wakes the trigger up
FOR EACH ROW	once per row	a statement that changes 10 rows fires the trigger 10 times
NEW / OLD	pseudo-rows	NEW = the incoming row (INSERT/UPDATE); OLD = the prior row (UPDATE/DELETE)

A helpful mental model: NEW and OLD are temporary one-row “variables” the database hands your trigger, holding the row that caused the trigger to fire.

A Worked Example, End to End

Let’s keep a running year-to-date sales total on the pubs bookstore database. Suppose titles starts like this (abbreviated):

title_id	title	ytd_sales
BU1032	The Busy Executive’s Database Guide	4095
BU1111	Cooking with Computers	3876

Step 1 — install the trigger (this runs once, like CREATE TABLE; nothing fires yet):

CREATE TRIGGER update_ytd_sales
AFTER INSERT ON "dbo.sales"
FOR EACH ROW
BEGIN
    UPDATE "dbo.titles"
    SET ytd_sales = COALESCE(ytd_sales, 0) + NEW.qty
    WHERE title_id = NEW.title_id;
END;

(COALESCE(ytd_sales, 0) guards against a NULL total: NULL + 20 would be NULL, a classic pitfall. And why the quotes? The SQLite conversion of pubs keeps the SQL Server schema prefix in each table’s name — the table is literally named dbo.sales — so we quote it to keep the dot from being read as a schema.table separator.)

Step 2 — a store records a sale:

INSERT INTO "dbo.sales" (stor_id, ord_num, ord_date, qty, payterms, title_id)
VALUES ('7066', 'QA7442.3', '2023-09-13', 20, 'Net 30', 'BU1032');

Step 3 — trace what the database does, in order:

1. The row is inserted into "dbo.sales" (it’s an AFTER trigger, so the insert happens first).
2. The insert event fires update_ytd_sales, with NEW.qty = 20 and NEW.title_id = 'BU1032'.
3. The trigger body runs its UPDATE, matching only the row WHERE title_id = 'BU1032'.
4. That row’s ytd_sales becomes 4095 + 20 = 4115.

Step 4 — verify the result:

SELECT title_id, ytd_sales FROM "dbo.titles" WHERE title_id = 'BU1032';

title_id	ytd_sales
BU1032	4115

BU1111 is untouched — the WHERE clause in the trigger body scoped the update to the one book that was sold. Notice that the application only issued a single INSERT; the consistency bookkeeping came for free.

Try it yourself: download pubs.db and run this in the sqlite3 shell or from Python’s sqlite3 module — the snippets above run as-written against it. If you build your own database instead, drop the dbo. prefixes and use plain sales/titles names; only the pubs conversion carries them.

Vetoing Bad Data with a BEFORE Trigger

AFTER triggers react; BEFORE triggers can refuse. In SQLite, RAISE(ABORT, message) cancels the triggering statement entirely:

CREATE TRIGGER check_positive_qty
BEFORE INSERT ON "dbo.sales"
FOR EACH ROW
WHEN NEW.qty <= 0
BEGIN
    SELECT RAISE(ABORT, 'sales quantity must be positive');
END;

Now INSERT INTO "dbo.sales" (...) VALUES (..., -5, ...) fails with sales quantity must be positive, and the bad row never lands in the table. The optional WHEN clause is a filter: the body only runs for rows that match it.

Common Pitfalls

• Forgetting the trigger exists. Triggers run silently; when a table seems to “change by itself,” check SELECT name, sql FROM sqlite_master WHERE type = 'trigger';.
• Trigger cascades. A trigger’s body can change tables that have their own triggers. Keep bodies small, and be wary of a trigger updating the table it fires on (recursion!).
• Doing application logic in triggers. Triggers are for integrity (totals, logs, validations) — not for sending emails or calling web services. If a rule belongs to one application rather than to the data itself, keep it in the application.
• Dialect differences. The event/NEW/OLD model is standard, but the veto syntax varies: SQLite uses RAISE(ABORT, ...), MySQL uses SIGNAL SQLSTATE '45000', and PostgreSQL attaches a trigger function. Check your engine’s documentation before porting a trigger.

Cleaning Up

Triggers persist like tables do. To remove one:

DROP TRIGGER IF EXISTS update_ytd_sales;

Submission

I encourage you to submit your answers to the questions (and ask your own questions!) using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section. You can find the link to the class notebook on the syllabus.