Triggers and Stored Logic

You will be able to make the database react to a change by itself: keep a total in sync, record history, skip or refuse a bad row, and make a view writable.

Constraints refuse bad rows; views read. A trigger is a block of SQL stored with a table that runs when a row is inserted, updated or deleted, and it can write anywhere.

Anatomy

CREATE TRIGGER rate_audit
AFTER UPDATE OF rate ON rooms
FOR EACH ROW
WHEN NEW.rate IS NOT OLD.rate
BEGIN
    INSERT INTO rate_log VALUES (OLD.id, OLD.rate, NEW.rate);
END;

• Timing: BEFORE or AFTER the row changes. INSTEAD OF is for views only.
• Event: INSERT, UPDATE or DELETE. UPDATE OF rate fires only when rate appears in the SET list.
• FOR EACH ROW: SQLite has no statement-level triggers, so a statement touching 100 rows runs the body 100 times.
• WHEN: a condition; the body is skipped when it is false.
• OLD and NEW: the row before and after. INSERT has only NEW, DELETE only OLD, UPDATE both. Using the missing one is an error.
• Body: one or more statements, each ending in ;. Triggers only see changes made after they exist.

With rooms holding (1, 90), UPDATE rooms SET rate = 90 logs nothing because WHEN is false, and SET rate = 110 logs 1|90|110. A BEFORE trigger cannot rewrite NEW; normalise with AFTER plus an UPDATE.

Keeping a total in sync

A stored total is fast to read and wrong once someone forgets to update it. Here campaigns.raised should equal the sum of its pledges.amount.

CREATE TRIGGER pledge_in AFTER INSERT ON pledges FOR EACH ROW
BEGIN
    UPDATE campaigns SET raised = raised + NEW.amount WHERE id = NEW.campaign_id;
END;

On INSERT the new amount is added; on DELETE the same idea runs with OLD and a minus. UPDATE is the trap, because the row has two amounts. Adding only NEW.amount double-counts:

-- wrong: raised = raised + NEW.amount
-- right: raised = raised + NEW.amount - OLD.amount

Pledges of 50 and 30, then the 30 changed to 80, give raised = 130 with the right version and 160 with the wrong one. Apply the difference.

Skipping or refusing a row

Inside a BEFORE trigger, SELECT RAISE(...) stops the row. Take a rule a CHECK cannot state, because it looks at other rows: at most 2 guests per room.

CREATE TRIGGER room_full BEFORE INSERT ON guests FOR EACH ROW
WHEN (SELECT COUNT(*) FROM guests WHERE room_id = NEW.room_id) >= 2
BEGIN
    SELECT RAISE(IGNORE);
END;

Insert (1,7,'a'),(2,7,'b'),(3,7,'c'),(4,8,'d') and the guests are a,b,d: RAISE(IGNORE) drops only the offending row, silently, and the statement carries on. Swap in RAISE(ABORT, 'room full') and you get Error: room full with the whole statement undone (0 rows). RAISE(FAIL, ...) raises the same error but keeps the rows already inserted (a,b). RAISE(ROLLBACK, ...) ends the whole transaction. A trigger runs inside its statement's transaction, so an error in the body undoes that statement.

Writable views

A view is read-only. An INSTEAD OF trigger replaces the write with whatever you choose: INSTEAD OF INSERT ON item_labels with the body INSERT INTO items VALUES (NEW.id, upper(NEW.label)); makes INSERT INTO item_labels VALUES (1, 'abc') store ABC.

What to watch

• Write cost. A bulk load fires every trigger per row.
• Hidden behaviour. Nothing in the application shows that an INSERT also updates another table; sqlite_master lists them.
• Order. Several triggers on one event run in no promised order.
• Recursion. recursive_triggers is off, so an AFTER UPDATE trigger that updates its own table fires once.
• No stored procedures. SQLite has no CREATE FUNCTION or CREATE PROCEDURE; PostgreSQL's PL/pgSQL trigger functions will not run here.

Common mistakes

• Using NEW in a DELETE trigger: the row has no new version and the statement fails.
• Adding NEW on UPDATE without subtracting OLD: the total drifts upward and a recount shows stale.
• Using RAISE(ABORT, ...) where a bad row should just be skipped: the script stops with an error instead of dropping that row.
• Creating the triggers after the statements they should observe: earlier rows are never counted.

Your exercise

Write four triggers that keep orders.total equal to the sum of quantity * price and drop non-positive quantities. The likeliest slip is the UPDATE trigger, which must apply a difference.