304 — Transactions, Views, and Triggers

Advanced

Learn to protect data integrity with transactions, simplify complex queries with views, and automate database actions with triggers. These three features are the foundation of reliable, maintainable database systems.

Learning Objectives

Use BEGIN, COMMIT, and ROLLBACK to group operations atomically

Understand ACID properties and why they matter

Create and use views to simplify complex queries

Build triggers that automatically respond to data changes

Combine transactions with triggers for reliable data processing

Step 1

What is a transaction?

A transaction groups multiple SQL statements into a single unit of work. You can commit the whole unit or roll it back as one piece. Start by seeing why this matters.

Commands to Run

sqlite3 ecommerce.db "SELECT id, stock_quantity FROM products WHERE id = 1;"

sqlite3 ecommerce.db "BEGIN; UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 1; INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 1, 1, (SELECT price FROM products WHERE id = 1)); COMMIT;"

sqlite3 ecommerce.db "SELECT id, stock_quantity FROM products WHERE id = 1;"

What This Does

When a customer buys a product, two things must happen together: (1) reduce stock and (2) add the order item. If the stock update succeeds but the order item insert fails (maybe invalid order_id), you've lost inventory with no record of where it went. BEGIN starts an explicit transaction and COMMIT makes the whole unit permanent. If one statement fails before COMMIT, SQLite stops at the failing statement and leaves the transaction open so your application can decide whether to fix the problem or issue ROLLBACK. In well-written application code, a failure in a multi-step unit should trigger ROLLBACK so none of the partial work is kept. That is atomicity — the 'A' in ACID.

Expected Outcome

First query shows the product's original stock quantity. After the transaction, stock_quantity is reduced by 1 and a new order_item row exists. Both changes happened together.

Pro Tips

1
Mental model: a transaction is like a Git commit — you stage changes, then commit them all at once
2
In SQLite, each individual statement outside a BEGIN/COMMIT is automatically wrapped in its own transaction
3
Transactions are essential whenever multiple related changes must succeed or fail together

Common Mistakes to Avoid

⚠️Forgetting to COMMIT — your changes are held in limbo and may be lost when the connection closes
⚠️Assuming each SQL statement is independent — without transactions, partial failures corrupt data

Was this step helpful?