Views, Triggers and Privacy - Database Cheat Sheet

Who sees what, one patient at a time: views that hide columns, masking with SUBSTR and ||, an age-band view for research, a change log written by an AFTER trigger, and a BEFORE trigger that refuses a bad charge.

On the sheet:

- Who sees what: one patient in the table and in three role views

- CREATE VIEW: save a query and hide columns

- Drop and rebuild: how to change a view in SQLite

- Always live, and read-only: what a view shows, and what it refuses

- Masking: SUBSTR and || hide part of a value

- De-identify with CASE: an age-band view for research

- Protect the stored data: encryption, hashing, PHI and HIPAA

- AFTER and BEFORE triggers: a change log, and RAISE(ABORT)

- CTE WITH: a named step inside one query

Two A4 pages, printed front and back, sized to print once and keep beside you while you work. Written in short sentences and plain English, for readers whose first language is not English and clearer for everyone else because of it.

It pairs with the Views: Reusable Reports & Security, Triggers & Stored Logic and Encryption & Privacy books, so you can learn each idea in full and then revise it here in two pages.

Every query on this sheet was run against a live database before the page was printed, so the result you read is the result you get. Instant PDF download. By M.R. Sterling.