数据库原理学习——MySql触发器详解

目录

一、什么是触发器（Trigger）？

二、触发器的特点

三、MySQL中触发器的用法、语法

1. 定义触发器  2. 触发器逻辑开始

3. 触发条件检查  4. 删除逻辑

5. 结束逻辑  6. DELIMITER 语句

四、示例演示触发器的执行过程

五、触发器的应用场景（六种场景，含示例代码）

补充：触发器如何关闭

总结

一、什么是触发器（Trigger）？

触发器（Trigger）是数据库中的一种特殊存储程序，它绑定到某张表（或视图）上，并在特定的数据库操作（如 INSERT、UPDATE 或 DELETE）发生时自动执行预定义的操作。触发器无需手动调用，是一种事件驱动的机制。

二、触发器的特点

1. 自动执行：一旦满足触发条件，触发器会在相关操作执行之前或之后自动运行。

2. 绑定表：每个触发器都与特定的表绑定，只对该表的操作有效。

3. 操作类型：常见触发器类型包括：

   • AFTER DELETE：在删除数据之后执行。

   • BEFORE DELETE：在删除数据之前执行。

   • AFTER UPDATE：在更新数据之后执行。

   • BEFORE UPDATE：在更新数据之前执行。

   • AFTER INSERT：在插入数据之后执行。

   • BEFORE INSERT：在插入数据之前执行。

4. 适用范围：

   • 触发器作用于每一行操作（FOR EACH ROW），或是整个语句的操作（FOR EACH STATEMENT，但在 MySQL 中不支持）。

三、MySQL中触发器的用法、语法

下面是一个用于清理数据的触发器代码示例，为了使大家更好的理解触发器的使用，下面将为大家逐帧分析每句代码的作用以及使用方法。

(题目：在社区表community中，当新增新的小区时；触发事件为当前新增小区这个insert操作，条件是：新增小区楼栋数量 大于20栋 且 住户不低于150人，若满足条件则：在访客记录表manual_record中查询当前所有已离开的访客记录，将离开时间（out_time）距离现在已经是一年以前的记录都删除； )

DELIMITER $$
​
CREATE TRIGGER update_is_leave_cleanup
AFTER INSERT ON community
FOR EACH ROW
BEGIN
  -- 检查新增小区楼栋数量是否 大于20栋 且 住户是否不低于150人
  IF NEW.term_count > 20 AND NEW.per_count >= 150 THEN
    -- 删除所有离开时间超过一年的访客记录
    DELETE FROM manual_record
    WHERE is_leave = 1
      AND out_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);
  END IF;
END$$
​
DELIMITER ;

这段代码定义了一个 MySQL 触发器，用于在对表 community执行 insert操作后自动执行特定逻辑。以下是逐步的解释：

1. 定义触发器

CREATE TRIGGER update_is_leave_cleanup
AFTER INSERTON community
FOR EACH ROW

• CREATE TRIGGER update_is_leave_cleanup：创建一个名为 update_is_leave_cleanup 的触发器。

• AFTER INSERT：表示在 community表执行 INSERT操作后触发该触发器。

• ON community：触发器绑定到 community表。

• FOR EACH ROW：触发器对 UPDATE 操作的每一行都生效（逐行触发）。

2. 触发器逻辑开始

BEGIN
...
end

• BEGIN 和 END 标志触发器主体，表示触发器的逻辑操作。

3. 触发条件检查

 -- 检查新增小区楼栋数量是否 大于20栋 且 住户是否不低于150人
  IF NEW.term_count > 20 AND NEW.per_count >= 150 THEN

• NEW.字段名：表示插入的新值。

• 条件含义：

  • 检查新增小区楼栋数量是否 大于20栋 且 住户是否不低于150人。

  • 如果 NEW.term_count > 20 且 NEW.per_count >= 150，说明当前新增记录符合条件。

2. OLD.字段名的补充：

• OLD 是在 DELETE 或 UPDATE 触发器中使用的，用于引用被修改或删除的 旧数据。
• 在 INSERT 触发器中，OLD 不可用，因为没有“旧数据”。

4. 删除逻辑

DELETE FROM manual_record
WHERE is_leave = 1
  AND out_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

• DELETE FROM manual_record：删除符合条件的记录。

条件 1：is_leave = 1：

• 仅删除已经离开的记录。

条件 2：out_time < DATE_SUB(NOW(), INTERVAL 1 YEAR)：

• out_time 是记录的离开时间。

• DATE_SUB(NOW(), INTERVAL 1 YEAR) 计算当前时间减去 1 年的日期。

• 如果 out_time 早于一年前，则认为该记录已过期，删除之。

5. 结束逻辑

END IF;
END$$

• END IF;：结束条件语句。

• END$$：标志触发器逻辑结束。

6. DELIMITER 语句

DELIMITER $$

• 默认的 MySQL 语句分隔符是 ;。

• 在创建触发器时，为了避免触发器内部的 ; 与默认分隔符冲突，使用 DELIMITER 改变分隔符（如 $$）。

• DELIMITER ;：在触发器定义完成后，将分隔符改回默认值。

7.功能总结

• 触发器目标：

  1. 检查新增小区是否满足指定条件。

  2. 删除manual_record表中所有满足以下条件的记录：

     ①is_leave = 1（表示访客已离开）。
     ②out_time 距离当前时间已有 1 年以前。

• 触发时机： 每当 community表中新增（楼栋数量大于20且住户不低于150人的）小区时，该触发器会自动执行。

四、示例演示触发器的执行过程

以下步骤将演示如何使用上述触发器。

1. 表结构准备

创建触发器涉及到两张表：community 和 manual_record。我们先定义表结构：

community 表

CREATE TABLE 
community ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255), term_count INT, -- 楼栋数量 
    per_count INT -- 住户数量 
);

manual_record 表

CREATE TABLE 
manual_record ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    is_leave TINYINT(1), -- 是否离开 (1: 已离开, 0: 未离开) 
    out_time DATETIME -- 离开时间 
);

2. 插入数据

在触发器触发前，我们需要确保 manual_record 表中有符合条件的数据：

向 manual_record 表插入数据

INSERT INTO manual_record (is_leave, out_time) 
VALUES (1, '2022-12-01 12:00:00'), -- 超过一年的离开记录 
(1, '2023-06-01 12:00:00'), -- 未超过一年的离开记录 
(0, '2023-12-01 12:00:00'); -- 未离开的记录

查看 manual_record 表内容

SELECT * FROM manual_record;

结果：

3. 触发器测试

向 community 表插入满足条件的记录

触发器的条件是 term_count > 20 且 per_count >= 150。执行以下插入语句：

INSERT INTO community (name, term_count, per_count) 
VALUES ('Example Community', 25, 200);

检查触发器执行结果

触发器应该已经被触发，并从 manual_record 表中删除所有离开时间超过一年的记录。

SELECT * FROM manual_record;

结果：

解释：

• ID 为 1 的记录已被删除，因为它的离开时间（2022-12-01）超过了一年。
• ID 为 2 和 3 的记录未被删除，因为它们不满足删除条件。

4. 插入不满足条件的记录

验证触发器不会在条件不满足时执行：

INSERT INTO community (name, term_count, per_count)
 VALUES ('Small Community', 15, 100);

再次检查 manual_record 表，结果没有变化，仍为：

解释： 触发器未触发，因为 term_count = 15 和 per_count = 100 不满足条件。

注意事项

1. 触发器性能：如果表数据量大，触发器中执行 DELETE 操作可能会影响性能。

2. 事务支持：如果 UPDATE 操作失败，触发器逻辑也不会执行。

3. 调试触发器：可以通过启用 MySQL 日志或手动检查数据变化来调试触发器。

4. 数据备份：删除记录操作是不可逆的，在执行触发器前确保数据已备份。

如有其他问题，可以进一步优化触发器逻辑！ 😊

 五、触发器的应用场景

以下是六个适合使用触发器的实际项目场景，包含基础代码；

1. 数据完整性和一致性

• 确保数据满足特定的业务规则，避免人为错误。

• 触发器代码示例①：在用户表插入新记录时，自动设置默认值或校验数据合法性。

  CREATE TRIGGER set_default_role
  BEFORE INSERT ON users
  FOR EACH ROW
  BEGIN
    IF NEW.role IS NULL THEN
      SET NEW.role = 'user';
    END IF;
  END;

2. 自动审计（Audit Log）

• 自动记录对表的增删改操作，追踪数据变化历史。

• 触发器代码示例②：在修改数据时，记录操作日志。

  CREATE TRIGGER log_updates
  AFTER UPDATE ON employees
  FOR EACH ROW
  BEGIN
    INSERT INTO audit_log (table_name, action, old_value, new_value, change_time)
    VALUES ('employees', 'UPDATE', OLD.salary, NEW.salary, NOW());
  END;

3. 级联操作

• 自动处理与主表相关的其他表数据，以保持数据一致性。

• 触发器代码示例③：在删除订单时，同时删除与该订单相关的所有明细记录。

  CREATE TRIGGER cascade_delete_order
  AFTER DELETE ON orders
  FOR EACH ROW
  BEGIN
    DELETE FROM order_details WHERE order_id = OLD.id;
  END;

4. 定期清理数据

• 对过期或冗余的数据进行自动清理。

• 触发器代码示例④：当用户状态更新为注销时，自动清理其关联的会话数据。

  CREATE TRIGGER cleanup_sessions
  AFTER UPDATE ON users
  FOR EACH ROW
  BEGIN
    IF NEW.status = 'deactivated' THEN
      DELETE FROM sessions WHERE user_id = NEW.id;
    END IF;
  END;

5. 复杂计算或衍生数据更新

• 根据某些操作自动更新相关的统计信息或衍生数据。

• 触发器代码示例⑤：当订单状态更新时，重新计算用户累计消费金额。

  CREATE TRIGGER recalculate_total_spent
  AFTER UPDATE ON orders
  FOR EACH ROW
  BEGIN
    UPDATE customers
    SET total_spent = (SELECT SUM(total_price) FROM orders WHERE customer_id = NEW.customer_id)
    WHERE id = NEW.customer_id;
  END;

6. 安全控制

• 限制对敏感数据的非法修改或记录潜在违规行为。

• 触发器代码示例⑥：防止员工工资被修改为负值。

  CREATE TRIGGER prevent_negative_salary
  BEFORE UPDATE ON employees
  FOR EACH ROW
  BEGIN
    IF NEW.salary < 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
  END;

六、触发器的优缺点

优点：

1. 自动化：无需手动触发或调用，减少代码冗余。

2. 提高数据一致性：通过预定义规则，确保数据满足业务逻辑。

3. 分离业务逻辑：将部分逻辑从应用程序移到数据库，集中管理。

4. 安全性增强：可以避免应用层的意外或恶意操作。

缺点：

1. 调试困难：触发器是隐式执行的，不易定位问题。

2. 性能开销：复杂的触发器逻辑可能对数据库性能造成影响，尤其是高并发场景。

3. 不可移植性：触发器依赖于特定数据库的实现，迁移到其他数据库可能需要重写。

4. 隐藏逻辑：触发器的自动执行特性可能让开发人员忽略其作用，导致意外行为。

补充：触发器如何关闭

在 MySQL 中，可以通过以下两种方法关闭触发器：

1. 禁用触发器

• 可以使用命令临时禁用触发器，以停止触发器的执行，而无需删除触发器定义。
• 需要注意的是，MySQL 不支持直接禁用单个触发器，但可以通过禁用整个表的触发器来达到类似的效果。

禁用所有触发器（全局禁用）

SET GLOBAL DISABLE_TRIGGERS = 1;

启用所有触发器（全局启用）

SET GLOBAL DISABLE_TRIGGERS = 0;

2. 删除触发器

如果需要永久关闭某个触发器，可以直接使用 DROP TRIGGER 命令删除触发器。

删除触发器的语法

DROP TRIGGER [IF EXISTS] trigger_name;

示例

假设有一个触发器 名称为：update_is_leave_cleanup，可以用以下命令删除：

DROP TRIGGER IF EXISTS update_is_leave_cleanup;

3. 触发器删除注意事项

1. 作用范围：触发器是绑定在特定表上的，删除触发器只会影响当前表，不会影响其他表。

2. 权限要求：删除触发器需要具有 SUPER 或 TRIGGER 权限。

3. 无法撤销：删除触发器是永久操作，触发器的定义不会被保存，因此建议在删除之前备份触发器定义。

4. 示例：完整操作流程

（1）查看现有触发器

可以查询数据库中的触发器列表，确认需要删除的触发器名称。

SHOW TRIGGERS;

（2）删除触发器

DROP TRIGGER IF EXISTS update_is_leave_cleanup;

（3）验证触发器是否已被删除

重新查询触发器列表，确认是否已经删除：

SHOW TRIGGERS;

触发器的使用建议：

• 临时关闭触发器：建议尽量避免直接删除触发器，尤其是在调试或临时停用的情况下，可以通过全局禁用的方式实现。
• 备份触发器：如果确实需要删除触发器，请提前备份触发器的定义，以便未来需要时快速恢复。

触发器的备份示例：

SHOW CREATE TRIGGER update_is_leave_cleanup \G;

此命令会显示触发器的创建语句，可以复制并保存以备后续使用。

总结

        触发器是一种强大的工具，用于增强数据库的自动化处理能力。它适合处理诸如数据校验、审计记录、自动清理、级联操作等任务，但应谨慎使用，避免复杂逻辑影响性能。如果业务需求涉及复杂逻辑，建议将部分逻辑移到应用程序中实现