MariaDB 教程 / 存储过程、函数与触发器:调用、事务和权限

存储过程通过 CALL 显式调用,存储函数在 SQL 表达式中返回一个值,触发器则随指定的数据表变更自动执行。三者都把逻辑保存在服务器端,但调用方式、权限身份和事务责任不同,不能只因都包含 SQL 就互相替代。

按调用方式与职责选择对象

对象 调用方式 本篇示例
存储过程 CALL 名称(参数) 显式调整商品库存
存储函数 在 SQL 表达式中使用名称(参数) 计算数量与单价的金额
触发器 INSERT、UPDATE 或 DELETE 自动触发 记录库存变化前后值

过程不一定修改数据,函数也不是只能做算术。本例刻意用不同职责展示差异。MariaDB 触发器按行执行,一条语句更新多行会产生相应的触发器工作;需要评估隐藏逻辑对排错和写入成本的影响。

先区分应用主动调用的操作与每次符合条件写表都会发生的行为,再判断规则应在应用验证,还是必须覆盖多个数据库写入路径。可结合VIEW 的复用与执行身份和调用者控制的事务边界比较。

准备权限与两张事务表

使用专用 appops_lab,由具有 CREATE ROUTINE、TRIGGER 及所需表权限的学习账户执行;调用例程还需要 EXECUTE。先参考账户与授权范围确认实际身份,不要给应用全局管理员权限。启用二进制日志的部署可能对函数创建有额外政策要求,应由管理员审查,而不是为练习放宽服务器全局信任设置。

先检查数据库归属及 routine_stock、routine_audit 是否存在。IF NOT EXISTS 不会证明已有库结构正确;同名对象已存在时检查定义和数据,不要覆盖。以下完整设置在 MariaDB 客户端执行:

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
  product_id INT NOT NULL PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
  audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock INT NOT NULL,
  new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);

两张表均使用支持事务的 InnoDB,库存更新与触发器写入的审计行才能在本实验中一起回滚。初始商品编号 1 的库存为八,审计表没有记录。如果换成非事务存储引擎,不能直接假定回滚行为相同。

完整创建函数、过程与触发器

下面脚本适用于 mariadb 命令行客户端。DELIMITER 改变客户端识别语句结束的方式,不是发送给服务器的 SQL。图形编辑器应使用脚本执行功能;Python 驱动应把每个完整 CREATE 语句单独发送,去掉 DELIMITER 行和结尾的 //,不能把含分号的过程体随意拆开。

DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
  IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
  END IF;
  RETURN ROUND(p_price * p_qty, 2);
END//

CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
  IF p_delta IS NULL OR p_delta = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
  END IF;
  UPDATE routine_stock SET stock = stock + p_delta
  WHERE product_id = p_product_id AND stock + p_delta >= 0;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
  END IF;
END//

CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
  IF OLD.stock <> NEW.stock THEN
    INSERT INTO routine_audit (product_id, old_stock, new_stock)
    VALUES (NEW.product_id, OLD.stock, NEW.stock);
  END IF;
END//
DELIMITER ;

line_total 拒绝 NULL 或负的价格、数量,返回两位小数的金额;数量零是允许的。较大数量和价格仍可能超出返回 DECIMAL 范围,应按业务重新评估。DETERMINISTIC 与 NO SQL 描述的是这个具体实现,修改逻辑后应保持声明真实,参见 CREATE FUNCTION 文档。

adjust_stock 仅在商品存在、变更量非零且结果非负时更新一行,否则通过 SIGNAL 报告业务错误。ROW_COUNT() 必须紧接 UPDATE 检查,插入其他语句可能改变它所描述的操作。参数与调用身份可查看 CREATE PROCEDURE 文档。

触发器只在 OLD.stock 与 NEW.stock 不同时插入变化记录。它是数据库内的变更记录练习,并非独立、防篡改的审计系统。CREATE TRIGGER 文档说明了触发时机及 OLD、NEW 值。

同时比较库存表与审计表的回滚、提交结果

SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;

函数结果应为 37.50。事务内库存变成五,审计行记录八变五;ROLLBACK 后库存恢复八,审计行数恢复零。自增编号在回滚后不必重新使用,因此审计编号有间隔并不证明回滚失败,应比较真实行和库存值。

START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;

提交后库存为六,只有一条已提交审计记录,内容为八变六。过程有意不包含内部 COMMIT,调用者可将它与其他工作合并成一个事务。触发器也在引发它的语句事务中运行,不独立提交;要回滚时,应在正确连接和事务边界内操作。

检查无效输入与执行身份边界

下面两条语句分别执行。第一条试图从当前六件库存中扣除 99,第二条传入负价格,两者均应报告 SQLSTATE 45000。观察错误后,确认库存仍为六,审计表仍只有一条已提交记录。

CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);

SQL SECURITY INVOKER 表示函数与过程使用调用者权限访问表。只有 EXECUTE 而没有 UPDATE 的账户,不能通过本过程获得修改库存的能力。若使用实际受限账户验证,应分别检查能调用无需读表的金额函数、不能更新库存这两种结果,而不是仅用管理员测试。

触发器采用其 DEFINER 的安全上下文,并非 INVOKER。应管理定义者账户、存续周期及必要表权限;不能因为过程按调用者权限执行,就推断触发器也采用相同身份。

触发器限制涉及事务控制、结果返回等操作,也应避免再次修改正被触发语句使用的表。保持触发器工作简短且与事务直接相关,不把长时间外部任务藏在普通行更新之后。

检查定义,并只删除确认可丢弃的对象

SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;

记录例程定义与安全上下文,并确认备份、恢复确实包含所需函数、过程和触发器。跨服务器恢复定义后,若 DEFINER 不存在或权限不合适,对象仍可能运行失败;定义成功不等于调用行为已验证。

仅当隔离练习结束且确认名称、归属和数据均可丢弃时,才执行下列清理。保留需要的定义和记录,避免把同名业务对象误删。

DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;

普通 ROLLBACK 不能恢复已删除对象。正式例程修改还需要协调调用方、部署顺序及恢复流程,不能把练习清理命令直接用于生产。