Notes › DBMS
Views, Stored Procedures and Triggers
Beyond plain SELECT/INSERT, databases let you store queries (views), programs (stored procedures and functions) and automatic reactions (triggers) inside the database itself. Examples use MySQL syntax; PostgreSQL differences are noted.
Sample tables:
employees(id, name, dept_id, salary, email)
departments(id, name)
salary_log(emp_id, old_salary, new_salary, changed_at)
Views — a saved query
A view is a virtual table defined by a SELECT. It stores no data; it runs the query when you use it.
CREATE VIEW dept_summary AS
SELECT d.name AS dept, COUNT(*) AS staff, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e JOIN departments d ON d.id = e.dept_id
GROUP BY d.name;
SELECT * FROM dept_summary WHERE staff > 5; -- use it like a table
Why use views
- Simplicity: hide a complex join behind a simple name.
- Security: give a user access to the view (e.g. without the
salarycolumn) but not the base table. - Stable interface: change the underlying tables while the view stays the same.
Updatable views: you can INSERT/UPDATE through a view only if it maps to one base table without GROUP BY, DISTINCT, aggregates or UNION. WITH CHECK OPTION stops updates that would make the row disappear from the view.
Materialized views
A materialized view stores the result physically (PostgreSQL, Oracle). Reads are fast; the data must be refreshed:
CREATE MATERIALIZED VIEW monthly_sales AS SELECT …; -- PostgreSQL
REFRESH MATERIALIZED VIEW monthly_sales;
| View | Materialized view | |
|---|---|---|
| Stores data | ❌ | ✅ |
| Always up to date | ✅ | ❌ until refreshed |
| Read speed | same as the query | fast |
| Use for | simplicity, security | dashboards, heavy reports |
Stored procedures — programs inside the DB
A named block of SQL with parameters, variables, IF, loops and error handling, run with CALL.
DELIMITER //
CREATE PROCEDURE give_raise(IN p_dept INT, IN p_percent DECIMAL(5,2), OUT p_count INT)
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percent / 100)
WHERE dept_id = p_dept;
SET p_count = ROW_COUNT();
END //
DELIMITER ;
CALL give_raise(3, 10, @n);
SELECT @n; -- rows updated
Parameter modes: IN (input), OUT (returned), INOUT (both).
Transfer money safely in a procedure
DELIMITER //
CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amt DECIMAL(10,2))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
START TRANSACTION;
UPDATE accounts SET balance = balance - amt WHERE id = from_id AND balance >= amt;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
END IF;
UPDATE accounts SET balance = balance + amt WHERE id = to_id;
COMMIT;
END //
DELIMITER ;
Functions — return one value, usable in queries
DELIMITER //
CREATE FUNCTION tax(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
RETURN CASE WHEN amount > 100000 THEN amount * 0.3 ELSE amount * 0.1 END;
END //
DELIMITER ;
SELECT name, salary, tax(salary) AS tax FROM employees;
| Stored procedure | Function | |
|---|---|---|
| Called with | CALL proc() |
inside SQL: SELECT f(x) |
| Returns | 0 or more values via OUT params / result sets | exactly one value (or a table) |
| Can change data / manage transactions | ✅ | usually ❌ (no side effects) |
Use in WHERE / SELECT |
❌ | ✅ |
Triggers — run automatically on a change
A trigger fires on INSERT, UPDATE or DELETE, BEFORE or AFTER the change, for each row. Inside it, OLD is the row before and NEW is the row after.
| Event | OLD |
NEW |
|---|---|---|
| INSERT | — | ✅ |
| UPDATE | ✅ | ✅ |
| DELETE | ✅ | — |
Audit log (AFTER UPDATE)
CREATE TRIGGER log_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_log(emp_id, old_salary, new_salary, changed_at)
SELECT OLD.id, OLD.salary, NEW.salary, NOW() FROM DUAL
WHERE OLD.salary <> NEW.salary;
Validate / clean data (BEFORE INSERT)
DELIMITER //
CREATE TRIGGER clean_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SET NEW.email = LOWER(TRIM(NEW.email));
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END //
DELIMITER ;
PostgreSQL writes the body as a trigger function (RETURNS trigger … RETURN NEW;) and attaches it with CREATE TRIGGER … EXECUTE FUNCTION fn();.
Use triggers for: audit logs, keeping derived columns in sync, enforcing rules every app must follow. Avoid: long chains of triggers firing triggers — hidden logic is hard to debug and slows every write.
Cursors — row-by-row processing
A cursor walks through a result set one row at a time inside a procedure (DECLARE c CURSOR FOR SELECT …; OPEN c; FETCH c INTO …; CLOSE c;). It is slow — prefer one set-based UPDATE … JOIN whenever possible.
Logic in the database or in the app?
| In the DB (procedures/triggers) | In the application |
|---|---|
| ✅ one round trip, close to the data | ✅ easier to test, version and debug |
| ✅ rules apply to every client | ✅ portable across databases |
| ❌ vendor-specific language | ❌ more network calls |
| Most modern web apps keep business logic in the app and use views/triggers sparingly (audits, constraints). |
Interview one-liners
- A view stores a query, not data; a materialized view stores data.
- Procedure:
CALL, can modify data and use transactions; function: returns a value, usable inSELECT. - Trigger = automatic procedure on INSERT/UPDATE/DELETE;
OLD/NEWhold the row. - Views help security by exposing only some columns.
- Related: SQL Essentials and Joins, Transactions and ACID.