数据库触发器快速入门

MySQL 触发器是一种存储在数据库中的程序，它可以在特定的数据库事件（如插入、更新或删除表中的行）发生时自动执行。触发器可以用来强制业务逻辑或数据完整性规则，例如自动更新相关表中的信息、维护审计日志等。

触发器类型

MySQL 支持以下几种类型的触发器：

• Before：在触发事件发生前执行。
• After：在触发事件完成后执行。

对于每个事件，触发器可以针对以下操作定义：

• INSERT：当向表中插入新行时触发。
• UPDATE：当更新表中的行时触发。
• DELETE：当从表中删除行时触发。

总共有六种不同的触发器组合：

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

触发器语法

创建触发器的基本语法如下：

CREATE TRIGGER trigger_name
trigger_time trigger_event ON table_name
[FOR EACH ROW]
trigger_body

其中：

• trigger_name 是触发器的名字。
• trigger_time 可以是 BEFORE 或 AFTER。
• trigger_event 可以是 INSERT, UPDATE, DELETE。
• table_name 是触发器关联的表。
• FOR EACH ROW 表示对于每一行数据都执行触发器体。
• trigger_body 是触发器执行的SQL代码块。

触发器中的特殊变量

在触发器体内，可以使用一些特殊的变量来引用行数据：

• OLD: 对于 BEFORE 类型的触发器，OLD 是一个虚拟行，包含修改前的行数据；对于 AFTER 类型的触发器，OLD 包含删除操作前的行数据。
• NEW: 对于 BEFORE 类型的触发器，NEW 是一个虚拟行，包含将要插入或更新的行数据；对于 AFTER 类型的触发器，NEW 包含插入或更新后的行数据。

注意事项

• 触发器可以导致复杂的事务逻辑，因此设计时应谨慎。
• 触发器可能导致性能问题，特别是当它们涉及到大量数据或复杂计算时。
• 触发器可以相互嵌套，这意味着一个触发器的动作可以触发另一个触发器。
• 在开发过程中，建议在生产环境之外测试触发器，以确保它们按预期工作。

新增触发器示例

示例 1: 更新部门表中的员工计数

每当向employees表中添加一名员工时，更新departments表中的员工计数。

DELIMITER $$

-- 创建一个在插入操作之后执行的触发器
CREATE TRIGGER update_department_count
AFTER INSERT ON employees  -- 在employees表上创建触发器
FOR EACH ROW  -- 对于每一条新插入的记录执行触发器
BEGIN
    -- 更新departments表中的num_employees字段，增加1
    UPDATE departments
    SET num_employees = num_employees + 1
    WHERE department_id = NEW.department_id;  -- 根据新插入的记录中的department_id更新
END$$

DELIMITER ;

示例 2: 删除员工时减少部门员工数

每当从employees表中删除一名员工时，减少departments表中的员工计数。

DELIMITER $$

-- 创建一个在删除操作之后执行的触发器
CREATE TRIGGER reduce_department_count
AFTER DELETE ON employees  -- 在employees表上创建触发器
FOR EACH ROW  -- 对于每一条被删除的记录执行触发器
BEGIN
    -- 更新departments表中的num_employees字段，减少1
    UPDATE departments
    SET num_employees = num_employees - 1
    WHERE department_id = OLD.department_id;  -- 根据被删除记录中的department_id更新
END$$

DELIMITER ;

解释：

• DELIMITER $$ 和 $$ 用于更改SQL语句的结束符，以便在触发器定义中正确处理BEGIN和END关键字。
• CREATE TRIGGER 语句定义了一个新的触发器。
• AFTER INSERT 或 AFTER DELETE 指定触发器将在插入或删除操作之后执行。
• ON employees 指定触发器作用于employees表。
• FOR EACH ROW 表示对于每次插入或删除的每条记录都执行一次触发器。
• BEGIN ... END 包含了触发器的具体执行逻辑。
• UPDATE departments 语句用于更新departments表中的数据。
• WHERE department_id = NEW.department_id 或 WHERE department_id = OLD.department_id 用于确定哪一行应该被更新，NEW表示新插入的行，OLD表示被删除的行。

删除触发器

在MySQL中删除触发器相对简单，你可以使用DROP TRIGGER语句来完成这一任务。如果你之前创建的触发器名为update_login_date，你可以使用如下命令来删除它：

DELIMITER ;

DROP TRIGGER IF EXISTS reduce_department_count;

DELIMITER $$

解释：

• DROP TRIGGER IF EXISTS update_login_date; 命令尝试删除名为update_login_date的触发器。IF EXISTS子句是可选的，但在不确定触发器是否存在的情况下使用它可以避免错误。
• DELIMITER ; 和 DELIMITER $$ 用于设置SQL语句的结束符，这与创建触发器时一样重要。

在执行上述命令后，触发器update_login_date将被删除，如果你再次尝试插入数据到system_oauth2_access_token表，将不会触发任何对system_users表的更新操作。

确保你在正确的数据库上下文中执行此操作，因为DROP TRIGGER命令作用于当前选择的数据库。如果触发器存在于特定的数据库中，请确保你已经选择了正确的数据库，或者在DROP TRIGGER命令中指定完整的触发器名称，包括数据库名，例如：

DROP TRIGGER IF EXISTS `your_database_name`.`update_login_date`;

这样可以避免误删其他数据库中的同名触发器。

查询触发器

使用SHOW TRIGGERS命令

你可以直接使用SHOW TRIGGERS命令来查看当前数据库中的所有触发器。如果你想要查看特定数据库中的触发器，可以在命令中指定数据库名称。

SHOW TRIGGERS;
-- 或者查看特定数据库中的触发器
SHOW TRIGGERS FROM your_database_name;

使用INFORMATION_SCHEMA.TRIGGERS视图

MySQL的INFORMATION_SCHEMA提供了一个名为TRIGGERS的视图，其中包含了关于数据库中所有触发器的信息。你可以通过查询这个视图来获取所有触发器的详细信息。

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

如果你想查看特定数据库中的触发器，可以添加一个WHERE子句来过滤结果。

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name';

使用系统表

在某些版本的MySQL中，触发器的信息也存储在系统表中，例如mysql.triggers。不过，这些表通常是隐藏的，并且可能需要管理员权限才能访问。以下是一个示例查询，但它可能不适用于所有MySQL安装：

SELECT * FROM mysql.triggers;

示例查询

这里是一个具体的示例查询，它展示了如何从INFORMATION_SCHEMA.TRIGGERS视图中检索触发器的信息，并只显示某些重要的列：

SELECT TRIGGER_NAME, TRIGGER_SCHEMA, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIME, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name';

此查询会显示触发器名称 (TRIGGER_NAME)、触发器所在的数据库 (TRIGGER_SCHEMA)、触发事件类型 (EVENT_MANIPULATION)、关联的表 (EVENT_OBJECT_TABLE)、触发时机 (ACTION_TIME) 以及触发器执行的SQL语句 (ACTION_STATEMENT)。

修改触发器

在MySQL中，修改触发器通常涉及两个步骤：首先删除旧的触发器，然后重新创建一个新的触发器。这是因为MySQL不直接支持修改触发器的语句，而是要求你先删除再重新创建。下面我将演示如何修改一个触发器。

假设你已经有了一个名为update_login_date的触发器，现在你想修改它的行为。例如，你可能想在更新system_users表的login_date字段时加上一些额外的逻辑。

假设原始触发器如下：

DELIMITER $$

CREATE TRIGGER update_login_date
AFTER INSERT ON system_oauth2_access_token
FOR EACH ROW
BEGIN
    UPDATE system_users
    SET login_date = CURRENT_TIMESTAMP
    WHERE user_id = NEW.user_id;
END$$

DELIMITER ;

修改触发器

假设你希望在更新login_date的同时，还要检查是否超过了某个特定的时间阈值（例如，只更新过去一周内没有登录过的用户的login_date）。以下是修改后的触发器定义：

DELIMITER $$

-- 删除旧的触发器
DROP TRIGGER IF EXISTS update_login_date;

-- 创建新的触发器
CREATE TRIGGER update_login_date
AFTER INSERT ON system_oauth2_access_token
FOR EACH ROW
BEGIN
    -- 检查用户的最后登录日期是否超过了一周
    IF (CURRENT_DATE() > DATE_ADD(NEW.last_login_date, INTERVAL 7 DAY)) THEN
        UPDATE system_users
        SET login_date = CURRENT_TIMESTAMP
        WHERE user_id = NEW.user_id;
    END IF;
END$$

DELIMITER ;

解释：

删除旧的触发器：

DROP TRIGGER IF EXISTS update_login_date;

这行代码确保如果已经存在名为update_login_date的触发器，则将其删除。

创建新的触发器：

CREATE TRIGGER update_login_date
AFTER INSERT ON system_oauth2_access_token
FOR EACH ROW
BEGIN
    -- 检查用户的最后登录日期是否超过了一周
    IF (CURRENT_DATE() > DATE_ADD(NEW.last_login_date, INTERVAL 7 DAY)) THEN
        UPDATE system_users
        SET login_date = CURRENT_TIMESTAMP
        WHERE user_id = NEW.user_id;
    END IF;
END$$

• • IF (CURRENT_DATE() > DATE_ADD(NEW.last_login_date, INTERVAL 7 DAY)) THEN：这行代码检查新插入的行中的last_login_date字段，判断是否在过去一周内没有登录过。
  • 如果条件成立，则更新system_users表中的login_date字段。
  • NEW.user_id：引用新插入行中的user_id字段。

结束符：

DELIMITER ;

恢复默认的SQL语句结束符。