Wallet, Pre-Made Meals & Calculators: Master Database Triggers, Procedures & Functions

Introduction

The author was asked by a colleague whether they could write a database trigger. While AI can generate code instantly, the author realized that relying on generated code without understanding the underlying principles leads to future pitfalls. This article therefore provides a hands-on guide to the database "three musketeers": triggers, stored procedures, and user-defined functions (UDFs), using MySQL for all examples.

1. Triggers: "You Move, I Move"

Concept

A trigger automatically executes predefined logic when a specific table event occurs (INSERT, UPDATE, DELETE). The analogy: family members watching your wallet—whenever money arrives, they immediately demand you "hand it over for savings." The wallet change triggers the savings action automatically.

Syntax Template

DELIMITER //
CREATE TRIGGER trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic (can call functions or execute multiple SQL)
  -- use NEW/OLD keywords to access new/old data
END //
DELIMITER ;

Practical Example: Automatic Savings Log

Two tables are created: my_wallet_ny (income records) and money_pot_mmbb (savings log). An AFTER INSERT trigger on my_wallet_ny inserts a row into money_pot_mmbb using NEW.amount, NEW.income_source, and NEW.income_time.

DELIMITER //
CREATE TRIGGER trg_my_wallet_after_insert AFTER INSERT ON my_wallet_ny
FOR EACH ROW
BEGIN
  INSERT INTO money_pot_mmbb (amount, remark, saved_time)
  VALUES (NEW.amount, NEW.income_source, NEW.income_time);
END //
DELIMITER ;

Test:

INSERT INTO my_wallet_ny (income_source, amount, income_time) VALUES ('大伯母', 500.00, NOW());

The savings table automatically receives a new record without manual intervention.

2. Stored Procedures: "Pre-Made Meal" for Batch Operations

Concept

A stored procedure bundles a series of SQL statements into a single callable unit. Analogy: instead of cooking step-by-step (wash rice, chop vegetables, stir-fry), you use a "pre-made meal"—just say "execute cooking process" and the whole sequence runs automatically.

Syntax Template

DELIMITER $$
CREATE PROCEDURE procedure_name(
  [IN|OUT|INOUT] param1 datatype,
  [IN|OUT|INOUT] param2 datatype,
  ...
) [characteristics]
BEGIN
  DECLARE variable datatype [DEFAULT value];
  SQL statements;
  control flow statements;
  exception handling;
  SELECT ...;
END $$
DELIMITER ;

Practical Example: Regional Power Revenue Calculation

A power_transactions table stores region, volume (MWh), price (¥/MWh), and date. The stored procedure GetPowerByDate takes IN parameters for region, start date, end date, and an OUT parameter total for the summed revenue ( SUM(volume * price)).

DELIMITER //
CREATE PROCEDURE GetPowerByDate(
  IN p_region VARCHAR(20),
  IN start_date DATE,
  IN end_date DATE,
  OUT total DECIMAL(10,2)
)
BEGIN
  SELECT SUM(volume * price) INTO total
  FROM power_transactions
  WHERE region = p_region
    AND transaction_date BETWEEN start_date AND end_date;
END //
DELIMITER ;

Call:

CALL GetPowerByDate('华中', '2025-06-01', '2025-06-03', @total);

Returns the total revenue for the specified region and date range in a single call.

3. User-Defined Functions: Custom Calculator for Specialized Statistics

Concept

A UDF is a "custom calculator" for logic that built-in functions cannot express. Analogy: a market vendor's calculator only shows total price, but you need "total minus odd cents plus discount"; you define your own calculation rule.

Syntax Template

DELIMITER //
CREATE FUNCTION function_name(parameter_list)
RETURNS return_type
BEGIN
  DECLARE variable datatype;
  SET variable = expression;
  IF condition THEN ... END IF;
  RETURN result;
END //
DELIMITER ;

Practical Example: Tiered Commission Calculation

Function fn_calculate_net_revenue takes transaction_volume (MWh) and unit_price (¥/MWh), computes gross amount, then applies tiered commission:

≤ 100,000: 2%

100,000–500,000: 2% on first 100k + 1.5% on excess

> 500,000: 2% on first 100k + 1.5% on next 400k + 1% on remainder

Returns net income rounded to two decimals.

DELIMITER //
CREATE FUNCTION fn_calculate_net_revenue(
  transaction_volume DECIMAL(8,2),
  unit_price DECIMAL(6,2)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE gross_amount DECIMAL(10,2);
  DECLARE commission DECIMAL(10,2);
  DECLARE net_income DECIMAL(10,2);
  SET gross_amount = transaction_volume * unit_price;
  IF gross_amount <= 100000 THEN
    SET commission = gross_amount * 0.02;
  ELSEIF gross_amount <= 500000 THEN
    SET commission = (100000 * 0.02) + (gross_amount - 100000) * 0.015;
  ELSE
    SET commission = (100000 * 0.02) + (400000 * 0.015) + (gross_amount - 500000) * 0.01;
  END IF;
  SET net_income = gross_amount - commission;
  RETURN ROUND(net_income, 2);
END //
DELIMITER ;

Calling the function with specific volume and price returns the net revenue after tiered commission, eliminating repetitive SQL for each calculation.

4. Choosing Among the Three: Quick Reference

The article concludes with a decision guide:

Triggers → automatic response to data changes (event-driven).

Stored Procedures → batch multi-step operations, reusable workflows.

User-Defined Functions → custom scalar calculations, especially for statistics and reporting.

Mastering these three tools reduces manual SQL writing, minimizes errors, and eliminates dependency on external search for routine database tasks.