Deep Thought

MySql Stored Objects

2 min read

Stored Objects in MySQL: Views, Procedures, Functions, Triggers, Events

Introduction

MySQL supports several stored objects that allow you to move logic closer to the data. This improves reuse, consistency, security, and performance.

This document explains:

  • Views
  • Stored Procedures
  • Stored Functions
  • Triggers
  • Events

For each object type you will find:

  • What it is
  • What it is used for
  • Creation and usage syntax
  • A meaningful real-world example

1. Views

What it is

A view is a stored SELECT statement that behaves like a virtual table.

What it is used for

  • Simplifying complex queries
  • Restricting access to sensitive data
  • Creating reusable reporting layers

Syntax

CREATE VIEW view_name AS
SELECT ...;

Usage:

SELECT * FROM view_name;

Example

CREATE VIEW v_employee_overview AS
SELECT id, name, department
FROM employees;

This view allows HR staff to see employee data without exposing salary information.

2. Stored Procedures

What it is

A stored procedure is a stored program that can execute multiple SQL statements.

What it is used for

  • Implementing business workflows
  • Handling transactions
  • Reducing application-side SQL complexity

Syntax

CREATE PROCEDURE procedure_name(IN param INT)
BEGIN
  SELECT * FROM table WHERE id = param;
END;

Call:

CALL procedure_name(1);

Example

CREATE PROCEDURE get_orders_by_customer(IN cust_id INT)
BEGIN
  SELECT * FROM orders WHERE customer_id = cust_id;
END;

3. Stored Functions

What it is

A stored function returns a single value and can be used inside SQL expressions.

What it is used for

  • Reusable calculations
  • Data formatting or transformation

Syntax

CREATE FUNCTION function_name(x INT)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN x * 2;
END;

Usage:

SELECT function_name(5);

Example

CREATE FUNCTION calculate_vat(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN price * 0.20;

4. Triggers

What it is

A trigger automatically executes when a table event occurs (INSERT, UPDATE, DELETE).

What it is used for

  • Enforcing data integrity
  • Auditing changes
  • Automatic updates of derived data

Syntax

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  SET NEW.column = value;
END;

Example

CREATE TRIGGER set_created_at
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

5. Events

What it is

An event is a scheduled task executed by MySQL’s event scheduler.

What it is used for

  • Periodic cleanup
  • Automated maintenance
  • Time-based processing

Syntax

CREATE EVENT event_name
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

Example

CREATE EVENT ev_cleanup_expired_tokens
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM password_reset_tokens
  WHERE expires_at < NOW();

Graph View

Backlinks