Views, Transactions and Triggers

Introduction

This last lesson covers the tools that make a database dependable in real applications: saved queries, changes that cannot be left half done, and rules that run automatically.

Views: a saved query

A view is a named query that you use like a table. It stores the query, not the data, so it always shows current rows.

Views hide complexity. Everyone can write SELECT * FROM order_summary without repeating four joins, and if the logic changes you fix it in one place. They can also expose only certain columns, which is useful for privacy.

Transactions: all or nothing

Imagine moving money between two accounts. You subtract from one and add to the other. If the program crashes in between, money vanishes. A transaction groups statements so they either all happen or none do.

• BEGIN starts the group.
• COMMIT makes the changes permanent.
• ROLLBACK throws them all away, as if nothing happened.

Try replacing COMMIT with ROLLBACK and run it again. The balances stay at 100 and 50.

Transactions are described by four promises, known as ACID: Atomic (all or nothing), Consistent (rules such as CHECK always hold), Isolated (parallel users do not see each other's half-finished work) and Durable (committed data survives a crash).

Upsert: insert or update

Often you want "add this row, or if it exists, update it". An upsert does that in one step with ON CONFLICT.

The second statement hits an existing home row, so it adds 1 to views instead of failing.

Triggers: rules that run by themselves

A trigger runs a statement automatically when something changes. Here, every time a product's stock changes, we write a line in a log table.

OLD is the row before the change and NEW the row after. Triggers are powerful for audit trails, but they run invisibly, so use them sparingly and document them.

Try it yourself

Create a view low_stock that lists the name and stock of products with stock under 50. Then select from it ordered by stock.

Summary

• A view is a saved query you can select from like a table.
• A transaction (BEGIN ... COMMIT or ROLLBACK) makes a group of changes all-or-nothing.
• INSERT ... ON CONFLICT DO UPDATE inserts a new row or updates the existing one.
• A trigger runs automatically after a change, using OLD and NEW row values.