MySQL编程进阶之：触发器中的复杂逻辑：如何调用存储过程和函数。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！我是你们的老朋友，人称“Bug终结者”的程序员老王。今天咱们来聊点MySQL里比较“高级”的玩意儿——触发器中的复杂逻辑，特别是怎么在触发器里调用存储过程和函数。这部分内容稍微有点绕，但只要跟紧我的步伐，保证你听完之后功力大增，以后写触发器再也不头疼！

触发器是个啥？简单回顾一下

先给新来的朋友们简单科普一下，什么是触发器。你可以把触发器想象成一个“暗哨”，它潜伏在你的数据库表旁边，时刻监视着表里的数据变化。当表里的数据发生特定的改变（比如插入、更新、删除），这个“暗哨”就会立刻跳出来，执行你预先设定好的一系列操作。

触发器就像一个自动化的流程，不用你手动去干预，数据库自己就能完成一些复杂的任务。

触发器能干啥？

数据验证： 确保插入或更新的数据符合你的规则。比如，年龄不能是负数，邮箱格式必须正确。
数据同步： 自动把一个表的数据同步到另一个表。
审计跟踪： 记录数据的修改历史，方便你以后追查问题。
复杂业务逻辑： 实现一些需要在数据变化时才能触发的复杂业务流程。

触发器里的复杂逻辑：为什么要调用存储过程和函数？

好了，现在进入正题。触发器本身的功能已经很强大了，但有时候，你需要在触发器里执行更复杂的逻辑，比如：

需要多次查询其他表的数据： 触发器里直接写SQL语句，代码会变得很臃肿，可读性很差。
需要执行一些复杂的计算： 比如计算订单的总金额，涉及到折扣、运费等等。
需要调用外部API： 比如发送邮件、短信通知。

这时候，存储过程和函数就派上用场了。它们可以把这些复杂的逻辑封装起来，让你的触发器代码更简洁、更易于维护。

存储过程和函数：好基友，一辈子

简单来说，存储过程就是一组预编译好的SQL语句，可以接受参数、执行一些操作，然后返回结果。函数也类似，但它必须返回一个值。

存储过程： 侧重于执行一系列操作，可以没有返回值，也可以有多个返回值。
函数： 侧重于计算并返回一个值，必须有返回值。

在触发器里调用存储过程：实战演练

咱们通过一个例子来说明如何在触发器里调用存储过程。假设我们有一个orders表和一个order_logs表。每次向orders表插入一条新订单，我们都希望在order_logs表里记录一条日志，包括订单ID、创建时间、订单状态等等。

创建orders表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    order_total DECIMAL(10, 2) NOT NULL,
    order_status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

创建order_logs表：

CREATE TABLE order_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    log_time DATETIME NOT NULL,
    log_message VARCHAR(255) NOT NULL
);

创建存储过程log_new_order：

DELIMITER //
CREATE PROCEDURE log_new_order(IN order_id INT)
BEGIN
    INSERT INTO order_logs (order_id, log_time, log_message)
    VALUES (order_id, NOW(), CONCAT('New order created with ID: ', order_id));
END //
DELIMITER ;

这个存储过程接受一个参数order_id，然后在order_logs表里插入一条日志。

创建触发器after_order_insert：
```
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    CALL log_new_order(NEW.order_id);
END //
DELIMITER ;
```
这个触发器会在每次向orders表插入一条新数据后执行。NEW是一个特殊的变量，它代表新插入的行。NEW.order_id就是新插入的订单的ID。CALL log_new_order(NEW.order_id)就是调用存储过程log_new_order，并将新订单的ID作为参数传递给它。
测试：
```
INSERT INTO orders (customer_id, order_date, order_total)
VALUES (1, NOW(), 100.00);
```
执行完这条SQL语句后，你会在orders表里看到一条新订单，同时在order_logs表里看到一条对应的日志。

在触发器里调用函数：更上一层楼

接下来，咱们再看看如何在触发器里调用函数。假设我们需要在每次更新订单状态时，记录订单状态的变更历史。

创建order_status_history表：

CREATE TABLE order_status_history (
    history_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    status_time DATETIME NOT NULL,
    old_status VARCHAR(20) NOT NULL,
    new_status VARCHAR(20) NOT NULL
);

创建函数get_order_status：
```
DELIMITER //
CREATE FUNCTION get_order_status(order_id INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE status VARCHAR(20);
    SELECT order_status INTO status FROM orders WHERE order_id = order_id;
    RETURN status;
END //
DELIMITER ;
```
这个函数接受一个参数order_id，然后从orders表里查询该订单的当前状态，并返回。DETERMINISTIC 关键字表示这个函数对于相同的输入，总是返回相同的结果。这对于函数的调用优化很重要。
创建触发器before_order_update：
```
DELIMITER //
CREATE TRIGGER before_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.order_status <> NEW.order_status THEN
        INSERT INTO order_status_history (order_id, status_time, old_status, new_status)
        VALUES (NEW.order_id, NOW(), OLD.order_status, NEW.order_status);
    END IF;
END //
DELIMITER ;
```
这个触发器会在每次更新orders表里的数据之前执行。OLD是一个特殊的变量，它代表更新前的行。OLD.order_status就是更新前的订单状态。NEW.order_status就是更新后的订单状态。如果订单状态发生了改变，就在order_status_history表里插入一条记录。
测试：
```
UPDATE orders SET order_status = 'Shipped' WHERE order_id = 1;
```
执行完这条SQL语句后，你会在order_status_history表里看到一条新的记录，记录了订单状态的变更历史。

一些注意事项：避免踩坑

权限问题： 确保你的用户有执行存储过程和函数的权限。
事务问题： 触发器是在一个事务里执行的，如果触发器里的代码出错，整个事务都会回滚。所以，要特别注意错误处理。
循环调用： 避免触发器和存储过程/函数之间相互调用，否则会陷入死循环。
性能问题： 触发器会增加数据库的负担，特别是当触发器里的逻辑很复杂时。所以，要尽量优化触发器里的代码，避免不必要的查询和计算。
避免在触发器里做耗时操作： 比如发送邮件、调用外部API。这些操作会阻塞数据库的执行，影响性能。可以考虑使用消息队列来异步处理这些操作。

更复杂的例子：多表关联和条件判断

现在咱们来一个更复杂的例子，展示一下如何在触发器里使用多表关联和条件判断。假设我们有一个products表和一个order_items表。每次向order_items表插入一条新的订单项，我们都希望更新products表的库存。如果库存不足，就阻止插入操作，并抛出一个错误。

创建products表：

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    product_price DECIMAL(10, 2) NOT NULL,
    product_stock INT NOT NULL DEFAULT 0
);

INSERT INTO products (product_name, product_price, product_stock) VALUES
('Laptop', 1200.00, 10),
('Mouse', 25.00, 100),
('Keyboard', 75.00, 50);

创建order_items表：

CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL
);

创建存储过程update_product_stock：

DELIMITER //
CREATE PROCEDURE update_product_stock(IN p_product_id INT, IN p_quantity INT)
BEGIN
    DECLARE current_stock INT;

    -- 获取当前库存
    SELECT product_stock INTO current_stock FROM products WHERE product_id = p_product_id;

    -- 检查库存是否足够
    IF current_stock >= p_quantity THEN
        -- 更新库存
        UPDATE products SET product_stock = product_stock - p_quantity WHERE product_id = p_product_id;
    ELSE
        -- 库存不足，抛出错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock!';
    END IF;
END //
DELIMITER ;

这个存储过程接受两个参数：product_id和quantity。它首先查询products表，获取该产品的当前库存。然后，判断库存是否足够。如果足够，就更新库存；否则，就抛出一个错误。SIGNAL SQLSTATE '45000' 可以自定义错误信息。

创建触发器before_order_item_insert：
```
DELIMITER //
CREATE TRIGGER before_order_item_insert
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    CALL update_product_stock(NEW.product_id, NEW.quantity);
END //
DELIMITER ;
```
这个触发器会在每次向order_items表插入一条新数据之前执行。NEW.product_id就是新插入的订单项的产品ID。NEW.quantity就是新插入的订单项的数量。CALL update_product_stock(NEW.product_id, NEW.quantity)就是调用存储过程update_product_stock，并将产品ID和数量作为参数传递给它。

测试：

-- 库存足够
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);

-- 库存不足
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 100);

第一次插入会成功，因为库存足够。第二次插入会失败，因为库存不足，触发器会抛出一个错误。

总结

今天我们一起学习了如何在MySQL触发器中调用存储过程和函数，并通过几个实际的例子进行了演示。希望大家能够掌握这些技巧，并在实际的项目中灵活运用。记住，触发器虽然强大，但也需要谨慎使用，避免滥用导致性能问题。

好了，今天的讲座就到这里。感谢大家的观看，我们下期再见！

发表回复 取消回复

发表回复取消回复