Triggers and Automation
In many database systems, stored procedures let you save and reuse blocks of SQL logic. SQLite doesn't support stored procedures, but it has a powerful alternative: triggers. Triggers are blocks of SQL that run automatically when specific events happen in your database. They're perfect for enforcing rules, keeping audit logs, and automating repetitive work.
What Are Triggers?
A trigger is a named piece of SQL that fires automatically in response to an INSERT, UPDATE, or DELETE on a specific table. You don't call triggers manually — they run on their own whenever the triggering event occurs.
AFTER INSERT Trigger
-- Create tables
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
product TEXT NOT NULL,
quantity INTEGER NOT NULL,
total REAL NOT NULL,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE order_log (
id INTEGER PRIMARY KEY,
order_id INTEGER,
message TEXT,
logged_at TEXT DEFAULT (datetime('now'))
);
-- Create a trigger that logs every new order
CREATE TRIGGER log_new_order
AFTER INSERT ON orders
BEGIN
INSERT INTO order_log (order_id, message)
VALUES (NEW.id, 'New order: ' || NEW.quantity || 'x ' || NEW.product || ' for ' || NEW.customer);
END;
-- Insert some orders (the trigger fires automatically)
INSERT INTO orders (id, customer, product, quantity, total)
VALUES (1, 'Alice', 'Laptop', 2, 1999.98);
INSERT INTO orders (id, customer, product, quantity, total)
VALUES (2, 'Bob', 'Mouse', 5, 124.95);
INSERT INTO orders (id, customer, product, quantity, total)
VALUES (3, 'Carol', 'Monitor', 1, 349.99);
-- Check the automatically generated log
SELECT * FROM order_log;
Inside a trigger, NEW refers to the row being inserted (or the new values in an UPDATE). The trigger ran three times — once for each INSERT — without any manual intervention.
BEFORE Triggers for Validation
BEFORE triggers run before the operation happens, letting you validate or modify data before it's written.
-- Create a products table
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE validation_errors (
id INTEGER PRIMARY KEY,
table_name TEXT,
message TEXT,
attempted_at TEXT DEFAULT (datetime('now'))
);
-- Prevent negative prices
CREATE TRIGGER validate_price
BEFORE INSERT ON products
WHEN NEW.price < 0
BEGIN
SELECT RAISE(ABORT, 'Price cannot be negative');
END;
-- Auto-correct stock to 0 if negative
CREATE TRIGGER fix_negative_stock
BEFORE INSERT ON products
WHEN NEW.stock < 0
BEGIN
SELECT RAISE(ABORT, 'Stock cannot be negative');
END;
-- This works fine
INSERT INTO products (id, name, price, stock) VALUES (1, 'Widget', 9.99, 100);
INSERT INTO products (id, name, price, stock) VALUES (2, 'Gadget', 19.99, 50);
-- This would be rejected by the trigger:
-- INSERT INTO products (id, name, price, stock) VALUES (3, 'Bad Item', -5.00, 10);
SELECT * FROM products;
RAISE(ABORT, 'message') stops the operation and raises an error. This is how triggers enforce business rules at the database level.
UPDATE Triggers for Audit Trails
Tracking changes is one of the most common uses for triggers. An audit trail records who changed what and when.
-- Create employees table
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
position TEXT NOT NULL,
salary INTEGER NOT NULL,
updated_at TEXT
);
-- Create audit trail table
CREATE TABLE salary_audit (
id INTEGER PRIMARY KEY,
employee_id INTEGER,
employee_name TEXT,
old_salary INTEGER,
new_salary INTEGER,
change_amount INTEGER,
changed_at TEXT DEFAULT (datetime('now'))
);
-- Trigger to track salary changes
CREATE TRIGGER track_salary_change
AFTER UPDATE OF salary ON employees
WHEN OLD.salary != NEW.salary
BEGIN
INSERT INTO salary_audit (employee_id, employee_name, old_salary, new_salary, change_amount)
VALUES (OLD.id, OLD.name, OLD.salary, NEW.salary, NEW.salary - OLD.salary);
UPDATE employees SET updated_at = datetime('now') WHERE id = NEW.id;
END;
-- Insert employees
INSERT INTO employees (id, name, position, salary)
VALUES
(1, 'Alice', 'Developer', 80000),
(2, 'Bob', 'Designer', 75000),
(3, 'Carol', 'Manager', 90000);
-- Give raises (triggers fire automatically)
UPDATE employees SET salary = 88000 WHERE name = 'Alice';
UPDATE employees SET salary = 78000 WHERE name = 'Bob';
UPDATE employees SET salary = 95000 WHERE name = 'Carol';
-- View the audit trail
SELECT * FROM salary_audit ORDER BY changed_at;
-- View updated employees
SELECT * FROM employees;
Inside an UPDATE trigger, OLD refers to the row's values before the update and NEW refers to the values after. The WHEN clause ensures the trigger only fires when the salary actually changes.
DELETE Triggers for Soft Deletes
Instead of permanently deleting records, you can use triggers to archive them.
-- Create active and archived customer tables
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
status TEXT DEFAULT 'active'
);
CREATE TABLE customers_archive (
id INTEGER,
name TEXT,
email TEXT,
original_status TEXT,
archived_at TEXT DEFAULT (datetime('now'))
);
-- Archive customers before deletion
CREATE TRIGGER archive_customer
BEFORE DELETE ON customers
BEGIN
INSERT INTO customers_archive (id, name, email, original_status)
VALUES (OLD.id, OLD.name, OLD.email, OLD.status);
END;
-- Insert customers
INSERT INTO customers (id, name, email)
VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com'),
(3, 'Carol', 'carol@example.com');
-- Delete a customer (trigger archives automatically)
DELETE FROM customers WHERE name = 'Bob';
-- Bob is gone from the main table
SELECT * FROM customers;
-- But preserved in the archive
SELECT * FROM customers_archive;
This pattern is called a soft delete. The data isn't truly lost — it's moved to an archive table where it can be recovered if needed.
Triggers for Maintaining Computed Values
Triggers can automatically keep derived values up to date.
-- Create a shopping cart system
CREATE TABLE cart_items (
id INTEGER PRIMARY KEY,
cart_id INTEGER NOT NULL,
product_name TEXT NOT NULL,
price REAL NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE cart_totals (
cart_id INTEGER PRIMARY KEY,
item_count INTEGER DEFAULT 0,
total_amount REAL DEFAULT 0.0
);
-- Initialize a cart
INSERT INTO cart_totals (cart_id) VALUES (1);
-- Trigger: update totals when items are added
CREATE TRIGGER update_cart_on_insert
AFTER INSERT ON cart_items
BEGIN
UPDATE cart_totals
SET item_count = (SELECT SUM(quantity) FROM cart_items WHERE cart_id = NEW.cart_id),
total_amount = (SELECT SUM(price * quantity) FROM cart_items WHERE cart_id = NEW.cart_id)
WHERE cart_id = NEW.cart_id;
END;
-- Trigger: update totals when items are removed
CREATE TRIGGER update_cart_on_delete
AFTER DELETE ON cart_items
BEGIN
UPDATE cart_totals
SET item_count = COALESCE((SELECT SUM(quantity) FROM cart_items WHERE cart_id = OLD.cart_id), 0),
total_amount = COALESCE((SELECT SUM(price * quantity) FROM cart_items WHERE cart_id = OLD.cart_id), 0.0)
WHERE cart_id = OLD.cart_id;
END;
-- Add items to the cart
INSERT INTO cart_items (id, cart_id, product_name, price, quantity)
VALUES (1, 1, 'Laptop', 999.99, 1);
INSERT INTO cart_items (id, cart_id, product_name, price, quantity)
VALUES (2, 1, 'Mouse', 24.99, 2);
INSERT INTO cart_items (id, cart_id, product_name, price, quantity)
VALUES (3, 1, 'Keyboard', 79.99, 1);
-- Check cart totals (automatically maintained)
SELECT * FROM cart_totals;
-- Remove an item
DELETE FROM cart_items WHERE product_name = 'Mouse';
-- Totals update automatically
SELECT * FROM cart_totals;
SELECT * FROM cart_items;
Managing Triggers
-- Create a simple table and trigger
CREATE TABLE notes (
id INTEGER PRIMARY KEY,
content TEXT,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TRIGGER note_created
AFTER INSERT ON notes
BEGIN
SELECT 1; -- No-op trigger for demonstration
END;
-- List all triggers in the database
SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';
-- Drop a trigger
DROP TRIGGER IF EXISTS note_created;
-- Verify it's gone
SELECT name FROM sqlite_master WHERE type = 'trigger';
-- Insert still works, just without the trigger
INSERT INTO notes (id, content) VALUES (1, 'Hello World');
SELECT * FROM notes;
Practice Exercises
Try these on your own:
- Create a trigger that logs all DELETE operations on an orders table
- Build a BEFORE INSERT trigger that rejects orders with quantity less than 1
- Create an audit trigger that tracks changes to an employee's position (not just salary)
- Build a trigger that automatically updates a "last_modified" timestamp column
- Create triggers that maintain a running count of active vs archived records
Key Takeaways
- Triggers run automatically in response to INSERT, UPDATE, or DELETE events
- BEFORE triggers can validate or reject data; AFTER triggers can log or propagate changes
- Use NEW to reference incoming data and OLD to reference existing data
- RAISE(ABORT, 'message') cancels the operation and returns an error
- Triggers are ideal for audit trails, validation, soft deletes, and maintaining computed values
- SQLite doesn't support stored procedures, but triggers cover most automation needs
Pro Tip: Don't overuse triggers. They add hidden behavior that can surprise other developers working with your database. Document your triggers well, and prefer application-level logic for complex business rules. Use triggers for simple, critical operations like audit logging and referential integrity.
Next Steps
Triggers handle automation at the row level. The next lesson introduces window functions — a different kind of power. Where GROUP BY collapses rows into summaries, window functions perform calculations across related rows while keeping every individual row visible. You will learn ROW_NUMBER, RANK, LAG, LEAD, and running totals — the tools behind leaderboards, time-series comparisons, and percentile rankings.