Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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

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

More DBMS notes