跳到主要内容

SQL 创建触发器

介绍​

在 SQL 中，触发器（Trigger） 是一种特殊的存储过程，它会在数据库中的特定事件（如插入、更新或删除）发生时自动执行。触发器通常用于维护数据的完整性、记录日志或执行复杂的业务逻辑。

触发器的主要特点包括：

  • 自动执行：触发器在满足特定条件时自动触发，无需手动调用。
  • 事件驱动：触发器与特定的事件（如 INSERT、UPDATE 或 DELETE）相关联。
  • 数据完整性：触发器可以用于强制执行业务规则或数据约束。

触发器的基本语法​

在 SQL 中，创建触发器的基本语法如下：

sql
CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW]
BEGIN
    -- 触发器逻辑
END;
  • trigger_name：触发器的名称。
  • BEFORE | AFTER：指定触发器是在事件之前还是之后执行。
  • INSERT | UPDATE | DELETE：指定触发器关联的事件类型。
  • table_name：触发器关联的表名。
  • FOR EACH ROW：指定触发器是行级触发器（对每一行数据执行）还是语句级触发器（对整个语句执行）。
  • BEGIN ... END：触发器的主体，包含触发器的逻辑。

触发器的类型​

触发器可以根据触发时机和事件类型进行分类：

  1. BEFORE 触发器：在事件发生之前执行。
  2. AFTER 触发器：在事件发生之后执行。
  3. INSERT 触发器：在插入数据时触发。
  4. UPDATE 触发器：在更新数据时触发。
  5. DELETE 触发器：在删除数据时触发。

创建触发器的示例​

示例 1：在插入数据时记录日志​

假设我们有一个 orders 表，每当有新订单插入时，我们希望将订单信息记录到 order_logs 表中。

sql
CREATE TRIGGER log_order_insert
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_logs (order_id, action, log_time)
    VALUES (NEW.order_id, 'INSERT', NOW());
END;

在这个示例中：

  • NEW.order_id 表示新插入的订单 ID。
  • NOW() 函数用于获取当前时间。

示例 2：在更新数据时验证库存​

假设我们有一个 products 表，每当更新库存时，我们希望确保库存数量不会低于 0。

sql
CREATE TRIGGER check_stock_before_update
BEFORE UPDATE
ON products
FOR EACH ROW
BEGIN
    IF NEW.stock_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存数量不能为负数';
    END IF;
END;

在这个示例中：

  • NEW.stock_quantity 表示更新后的库存数量。
  • SIGNAL SQLSTATE 用于抛出自定义错误。

触发器的实际应用场景​

触发器在数据库中有广泛的应用场景，以下是一些常见的例子：

  1. 数据完整性检查：在插入或更新数据时，触发器可以用于检查数据的有效性，确保数据符合业务规则。
  2. 日志记录：触发器可以用于自动记录数据变更的历史，便于审计和追踪。
  3. 级联操作：触发器可以用于在删除或更新主表数据时，自动更新或删除相关子表的数据。
  4. 自动计算：触发器可以用于在数据变更时自动计算并更新相关字段的值。

总结​

触发器是 SQL 中一个强大的工具，它可以帮助我们自动化数据库操作，确保数据的完整性和一致性。通过本文，你已经学习了如何创建触发器，并了解了触发器的基本语法和实际应用场景。

提示

在实际使用触发器时，建议谨慎设计触发器的逻辑，避免复杂的嵌套触发器，以免影响数据库性能。

附加资源​

练习​

  1. 创建一个触发器，在删除 customers 表中的记录时，自动删除与该客户相关的所有订单。
  2. 创建一个触发器，在更新 employees 表中的工资时，自动记录工资变更的历史。

通过完成这些练习，你将进一步掌握触发器的使用技巧。