Master MySQL Stored Procedures, Events, and Triggers for Automated Data Workflows

Introduction

Developers and data analysts often need MySQL's advanced features—stored procedures, events, and triggers—to automate reporting, real‑time monitoring, and other business tasks without relying on external scripts.

1. Stored Procedures

What is a stored procedure and why use it?

Encapsulates a series of SQL statements under a name.

Stored in the database, similar to a function but without a return value.

Benefits: reusable code, parameter passing, reduced network traffic, higher efficiency.

Creating, viewing, calling, and dropping a procedure

create procedure proc_name()
begin
  -- SQL statements;
end

View procedures: show procedure status; Call a procedure: call proc_name(); Drop a procedure (if it exists):

drop procedure if exists proc_name;

Variables in procedures

Two kinds of variables:

System variables: @@var_name User variables: @var_name Declare a variable:

declare var_name var_type [default default_value];

Variable assignment and control structures

set var_name = value;

set var_name := value;

If‑else syntax:

if condition1 then
  sql_statement;
else if condition2 then
  sql_statement;
else
  sql_statement;
end if;

Parameter passing

Three parameter modes: in: input only. out: output only (acts as return value). inout: both input and output.

in|out|inout param_name param_type

Loops

While‑loop syntax:

while condition do
  sql_statement;
end while;

Example: sum of numbers from 1 to 100, and sum from 1 to n (input parameter).

2. Events (Timers)

What is an event and when to use it?

An event is a scheduled execution of SQL code, useful for periodic tasks such as daily report updates or batch data processing.

Event creation syntax

create event [if not exists] event_name
  on schedule at timestamp + interval N unit   -- one‑time execution
  -- or
  on schedule every N unit starts timestamp ends timestamp   -- periodic execution
  do
    sql_statement;

Key options: on completion preserve keeps the event after it runs; default is not preserve (auto‑deletes). enable | disable | disable on slave controls activation. comment 'text' adds a description.

Activating events

SET GLOBAL event_scheduler = 1;

To keep an event after execution:

ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE;

Common pitfalls

Events must be enabled; otherwise they won’t run.

Changing the delimiter may be required for multi‑statement events.

Events cannot accept parameters directly, but can invoke stored procedures.

3. Triggers

What is a trigger and typical use cases?

Triggers automatically fire on row‑level INSERT, UPDATE, or DELETE operations, enabling data synchronization, validation, or audit logging.

Creating, viewing, and dropping a trigger

CREATE TRIGGER trigger_name
  BEFORE|AFTER INSERT|UPDATE|DELETE
  ON table_name FOR EACH ROW
BEGIN
  -- trigger_body;
END;

View triggers: SHOW TRIGGERS; Drop a trigger:

DROP TRIGGER database_name.trigger_name;

Example: updating inventory on new orders

Tables:

CREATE TABLE goods(gid INT, name VARCHAR(10), num SMALLINT);
CREATE TABLE orders(oid INT, gid INT, buy_num SMALLINT);
INSERT INTO goods VALUES (1,'cat',20),(2,'dog',90),(3,'pig',26);

Trigger to decrement stock after an order insert:

CREATE TRIGGER trg_after_insert_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE goods SET num = num - NEW.buy_num WHERE gid = NEW.gid;
END;

Row variables

Inside a trigger, NEW refers to the incoming row and OLD to the existing row (for UPDATE/DELETE).

Advanced scenarios

Prevent overselling by adjusting buy_num when it exceeds available stock (using BEFORE trigger).

Restore inventory when an order is deleted (AFTER DELETE trigger).

Synchronize related tables on UPDATE.

These examples demonstrate how MySQL’s stored procedures, events, and triggers can be combined to build robust, data‑layer automation without external scripting.