MySQL触发器

封面图 • 2025-05-12 • MySQL

在 MySQL 中，触发器（Trigger）是一种特殊的存储过程，它会在特定的表上执行某些操作时自动触发。触发器通常用于在插入、更新或删除数据时执行一些额外的操作，例如日志记录、数据验证或计算衍生值。

### 触发器的基本语法

```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`：触发器作用的表。
- `[BEGIN] ... [END]`：触发器的逻辑代码块。

---

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

假设我们有一个 `users` 表，我们希望在每次向该表插入新用户时，将相关信息记录到一个日志表中。

```sql
-- 创建日志表
CREATE TABLE user_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(10),
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
DELIMITER $$

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_log (action, user_id)
    VALUES ('INSERT', NEW.id);
END$$

DELIMITER ;
```

#### 解释：
- `AFTER INSERT`：触发器在插入操作完成后执行。
- `NEW.id`：表示新插入的行的主键值。
- `user_log` 是日志表，记录了插入操作的用户 ID 和时间戳。

---

### 示例 2：在更新数据时验证数据完整性

假设我们有一个 `orders` 表，我们希望在更新订单状态时确保新状态是合法的。

```sql
-- 创建触发器
DELIMITER $$

CREATE TRIGGER before_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NEW.status NOT IN ('PENDING', 'SHIPPED', 'DELIVERED') THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Invalid status value';
    END IF;
END$$

DELIMITER ;
```

#### 解释：
- `BEFORE UPDATE`：触发器在更新操作开始前执行。
- `SIGNAL SQLSTATE`：如果新状态不合法，则抛出错误，阻止更新操作。

---

### 示例 3：在删除数据时记录被删除的数据

假设我们有一个 `products` 表，我们希望在删除某条产品记录时，将被删除的产品信息记录到另一个表中。

```sql
-- 创建记录表
CREATE TABLE deleted_products (
    id INT,
    name VARCHAR(100),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
DELIMITER $$

CREATE TRIGGER before_product_delete
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO deleted_products (id, name)
    VALUES (OLD.id, OLD.name);
END$$

DELIMITER ;
```

#### 解释：
- `BEFORE DELETE`：触发器在删除操作开始前执行。
- `OLD.id` 和 `OLD.name`：表示被删除的行的字段值。

---

### 注意事项
1. **触发器不能直接调用其他存储过程**：触发器只能包含 SQL 语句，不能直接调用存储过程。
2. **性能影响**：触发器会增加数据库的开销，尤其是在大量数据操作时，应谨慎使用。
3. **递归触发器**：避免触发器之间互相触发，可能导致无限循环。

通过以上示例和注意事项，您可以根据实际需求设计合适的触发器。