MySQL触发器之：`触发器`与`存储过程`：`触发器`如何调用`存储过程`。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始今天的讲座，主题是MySQL触发器如何调用存储过程。

MySQL触发器与存储过程：简介与区别

在深入探讨触发器如何调用存储过程之前，我们先来简单了解一下这两个概念，以及它们之间的主要区别。

存储过程 (Stored Procedure): 存储过程是预编译的SQL语句集合，存储在数据库服务器中。你可以通过存储过程的名称来调用它，并传递参数。存储过程可以包含复杂的逻辑，如条件判断、循环等，从而提高数据库操作的效率和安全性。
触发器 (Trigger): 触发器是一种特殊的存储过程，它与特定的表相关联，并在特定的数据库事件（如INSERT、UPDATE、DELETE）发生时自动执行。触发器用于在数据修改前后执行一些额外的操作，例如数据验证、审计记录、维护数据一致性等。

主要区别：

特性	存储过程	触发器
执行方式	显式调用，通过`CALL`语句	隐式执行，由数据库事件触发
关联对象	无特定关联对象	与特定的表关联
执行时机	由用户决定	由数据库事件决定
返回值	可以有返回值（`OUT`参数）	通常没有返回值
目的	执行一系列数据库操作，简化复杂逻辑	在数据修改前后执行额外的操作，维护数据完整性
性能	提高数据库操作效率	可能影响数据库操作性能，需谨慎使用

触发器调用存储过程的必要性

在某些情况下，触发器需要执行的操作比较复杂，或者需要在多个触发器中重复使用相同的逻辑。这时，将这些操作封装到一个存储过程中，然后在触发器中调用该存储过程，可以提高代码的可维护性和重用性。

例如，假设我们需要在每次向orders表插入新记录时，自动更新customers表中该客户的last_order_date字段。我们可以创建一个存储过程来实现更新customers表的功能，然后在orders表的AFTER INSERT触发器中调用该存储过程。

触发器调用存储过程的语法

在MySQL中，触发器调用存储过程的语法很简单，只需要在触发器的BEGIN...END块中使用CALL语句即可。

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
  CALL procedure_name(parameter1, parameter2, ...);
END;

trigger_name: 触发器的名称。
trigger_time: 触发器的执行时机，可以是BEFORE或AFTER。
trigger_event: 触发器触发的数据库事件，可以是INSERT、UPDATE或DELETE。
table_name: 与触发器关联的表。
procedure_name: 要调用的存储过程的名称。
parameter1, parameter2, ...: 传递给存储过程的参数。

触发器调用存储过程的示例

我们创建一个简单的例子来说明如何实现触发器调用存储过程，场景是更新客户最后订单日期：

创建customers表：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(255) NOT NULL,
    last_order_date DATE NULL
);

创建orders表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

创建存储过程update_last_order_date：

这个存储过程接收customer_id作为输入参数，并更新customers表中该客户的last_order_date字段。

DELIMITER //
CREATE PROCEDURE update_last_order_date(IN cust_id INT)
BEGIN
    UPDATE customers
    SET last_order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = cust_id)
    WHERE customer_id = cust_id;
END //
DELIMITER ;

创建触发器update_customer_last_order：

这个触发器在每次向orders表插入新记录后，调用update_last_order_date存储过程，更新customers表中该客户的last_order_date字段。
```
DELIMITER //
CREATE TRIGGER update_customer_last_order
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
    CALL update_last_order_date(NEW.customer_id);
END //
DELIMITER ;
```

现在，我们可以插入一些数据来测试这个触发器和存储过程。

INSERT INTO customers (customer_name) VALUES ('Alice'), ('Bob');

INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-01-15'), (2, '2023-02-20');

INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-03-10');

查询customers表，可以看到last_order_date字段已经更新：

SELECT * FROM customers;

结果：

customer_id	customer_name	last_order_date
1	Alice	2023-03-10
2	Bob	2023-02-20

使用OLD和NEW关键字

在触发器中，OLD和NEW是两个特殊的关键字，用于引用触发事件发生前后的数据。

OLD: 用于引用UPDATE和DELETE事件发生前的记录。
NEW: 用于引用INSERT和UPDATE事件发生后的记录。

例如，在UPDATE触发器中，OLD.column_name表示更新前的column_name的值，NEW.column_name表示更新后的column_name的值。

假设我们有一个products表，其中包含product_id、product_name和price字段。我们想要在每次更新products表的price字段时，记录更新前后的价格到product_price_history表中。

创建products表：

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

创建product_price_history表：

CREATE TABLE product_price_history (
    history_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    old_price DECIMAL(10, 2) NOT NULL,
    new_price DECIMAL(10, 2) NOT NULL,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建存储过程log_price_change：

DELIMITER //
CREATE PROCEDURE log_price_change(
    IN prod_id INT,
    IN old_price DECIMAL(10, 2),
    IN new_price DECIMAL(10, 2)
)
BEGIN
    INSERT INTO product_price_history (product_id, old_price, new_price)
    VALUES (prod_id, old_price, new_price);
END //
DELIMITER ;

创建触发器log_product_price_update：

DELIMITER //
CREATE TRIGGER log_product_price_update
AFTER UPDATE
ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        CALL log_price_change(NEW.product_id, OLD.price, NEW.price);
    END IF;
END //
DELIMITER ;

在这个例子中，IF OLD.price <> NEW.price THEN 语句只在价格发生变化时才调用存储过程。这样可以避免不必要的记录。

错误处理与事务管理

在触发器中调用存储过程时，需要特别注意错误处理和事务管理。如果存储过程执行失败，触发器也会失败，并且可能会导致数据不一致。

错误处理： 在存储过程中，可以使用DECLARE EXIT HANDLER语句来处理异常。例如，如果存储过程中发生SQLSTATE '23000' (违反完整性约束) 错误，可以回滚事务并记录错误日志。
事务管理： 触发器本身运行在一个事务中。如果在触发器中调用存储过程，并且存储过程也执行了事务操作（如START TRANSACTION、COMMIT、ROLLBACK），那么可能会导致事务嵌套。MySQL不支持嵌套事务，因此需要谨慎处理。一种常见的做法是在存储过程中不显式地进行事务管理，而是依赖于触发器所在的事务。

触发器调用存储过程的注意事项

性能： 触发器会增加数据库的负载，特别是当触发器中的逻辑比较复杂时。因此，应该尽量简化触发器的逻辑，避免在触发器中执行耗时的操作。如果可能，可以将一些操作移到应用程序中执行。
递归调用： 要避免触发器和存储过程之间的递归调用，这会导致无限循环，最终导致数据库崩溃。
权限： 确保触发器和存储过程具有执行所需的权限。
测试： 在生产环境中部署触发器之前，应该进行充分的测试，以确保触发器的行为符合预期。

示例：带异常处理的存储过程

以下是一个带有异常处理的存储过程的示例，它用于向accounts表中存入资金。如果存款金额为负数，则会抛出一个异常，并回滚事务。

DELIMITER //
CREATE PROCEDURE deposit(IN acc_id INT, IN amount DECIMAL(10, 2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF amount <= 0 THEN
       SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = '存款金额必须大于0';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE account_id = acc_id;

    COMMIT;
END //
DELIMITER ;

总结
触发器通过CALL语句调用存储过程，实现复杂的数据操作和业务逻辑。需要注意性能、错误处理和事务管理，避免递归调用和权限问题。充分的测试对于确保触发器按预期工作至关重要。

发表回复 取消回复

发表回复取消回复