MySQL触发器教程（01）：创建触发器

介绍

MySQL 中的触发器（Trigger）是一种特殊的数据库对象，它在指定的数据库表上定义了一组操作，当表上的某些事件（如插入、更新、删除等）发生时，自动触发这些操作的执行。CREATE TRIGGER 是 MySQL 提供的创建触发器的语法，它允许用户在数据库表上创建自定义的触发器。

语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
trigger_body

其中，关键字解释如下:

• trigger_name: 触发器的名称，用户自定义的唯一名称。
• {BEFORE | AFTER}: 触发器的触发时机，可以是 BEFORE（在事件之前触发）或 AFTER（在事件之后触发）。
• {INSERT | UPDATE | DELETE}: 触发器所监听的事件，可以是 INSERT（插入操作）、UPDATE（更新操作）或 DELETE（删除操作）。
• table_name: 触发器所关联的数据库表名。
• FOR EACH ROW: 表示触发器的作用范围是每一行，即每当表中的一行数据发生指定的事件时，触发器都会被触发。
• trigger_body: 触发器的执行体，包含一系列的 SQL 语句，用于定义触发器在触发时所执行的操作。

使用场景

MySQL 触发器适用于许多场景，例如：

• 数据验证和约束：可以通过触发器在数据插入、更新或删除前后进行验证和约束，确保数据库中的数据满足特定的业务规则和限制条件。
• 数据补全和处理：可以通过触发器在数据插入或更新前后进行自动补全和处理，例如自动生成序列号、计算字段值等。
• 数据审计和日志记录：可以通过触发器在数据插入、更新或删除时记录审计日志，用于追踪数据变更历史和安全审计。
• 数据复制和同步：可以通过触发器在数据插入、更新或删除时自动将数据复制到其他表或数据库，实现数据的同步和复制。

示例

下面是一个使用 MySQL CREATE TRIGGER 创建触发器的示例，假设有一个名为 orders 的数据库表，包含以下结构：

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(10),
  total_amount DECIMAL(10, 2),
  status VARCHAR(20)
);

我们创建一个触发器，当在 orders 表中插入新数据时，在插入前自动设置订单号，并在插入后更新订单状态为 “NEW”。触发器的定义如下：

DELIMITER //
CREATE TRIGGER before_insert_order
BEFORE INSERT ON orders FOR EACH ROW
BEGIN
  SET NEW.order_number = CONCAT('ORD', LPAD(NEW.id, 5, '0'));
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER after_insert_order
AFTER INSERT ON orders FOR EACH ROW
BEGIN
  UPDATE orders SET status = 'NEW' WHERE id = NEW.id;
END;
//
DELIMITER ;

在上面的示例中，我们创建了两个触发器，一个是 before_insert_order，在订单数据插入前会自动设置订单号；另一个是 after_insert_order，在订单数据插入后会自动更新订单状态为 “NEW”。

假设我们执行以下插入操作：

INSERT INTO orders (total_amount, status) VALUES (100.00, 'PENDING');

触发器将会在插入前自动设置订单号为 “ORD00001”，在插入后自动更新订单状态为 “NEW”。

结论

MySQL 的触发器是一种强大的数据库特性，可以在数据库表上定义一组操作，自动在特定事件发生时触发执行。通过 CREATE TRIGGER 语法，我们可以创建自定义的触发器，用于实现数据验证、数据处理、数据审计等业务需求。但需要注意，滥用触发器可能导致性能问题和复杂性增加，因此在使用触发器时应慎重考虑，并遵循最佳实践。