mysql的触发器是什么级的-mysql教程-PHP中文网

mysql的触发器是行级的。按照SQL标准，触发器可以分为两种：1、行级触发器，对于修改的每一行数据都会激活一次，如果一个语句插入了100行数据，将会调用触发器100次；2、语句级触发器，针对每个语句激活一次，一个插入100行数据的语句只会调用一次触发器。而MySQL中只支持行级触发器，不支持预语句级触发器。

mysql的触发器是什么级的

本教程操作环境：windows7系统、mysql8版本、Dell G3电脑。

触发器概述

MySQL 触发器（trigger）是一种存储程序，它和一个指定的表相关联，当该表中的数据发生变化（增加、更新、删除）时自动执行。这些修改数据行的操作被称为触发器事件，例如 INSERT 或者 LOAD DATA 等插入数据的语句可以激活插入触发器。

按照 SQL 标准，触发器可以分为行级触发器（row-level trigger）和语句级触发器（ statement-level trigger）。

行级触发器对于修改的每一行数据都会激活一次，如果一个语句插入了 100 行数据，将会调用触发器 100 次；
语句级触发器针对每个语句激活一次，一个插入 100 行数据的语句只会调用一次触发器。
MySQL 只支持行级触发器，不支持预语句级触发器。

不同事件可以激活不同类型的触发器。INSERT 事件触发器用于插入数据的操作，包括 INSERT、LOAD DATA、REPLACE 语句等；UPDATE 事件触发器用于更新操作，例如 UPDATE 语句；DELETE 事件触发器用于删除操作，例如 DELETE 和 REPLACE 语句等，DROP TABLE 和 TRUNCATE TABLE 语句不会激活删除触发器。

另外，MySQL 触发器可以在触发事件之前或者之后执行，分别称为 BEFORE 触发器和 AFTER 触发器。这两种触发时机可以和不同的触发事件进行组合，例如 BEFORE INSERT 触发器或者 AFTER UPDATE 触发器。

MySQL 触发器的优点包括：

记录并审核用户对表中数据的修改操作，实现审计功能；
实现比检查约束更复杂的完整性约束，例如禁止非业务时间的数据操作；
实现某种业务逻辑，例如增加或删除员工时自动更新部门中的人数；
同步实时地复制表中的数据。

虽然触发器功能强大，但是它也存在一些缺点：

触发器会增加数据库结构的复杂度，而且触发器对应用程序不可见，难以调试；
触发器需要占用更多的数据库服务器资源，尽量使用数据库提供的非空、唯一、检查约束等；
触发器不能接收参数，只能基于当前的触发对象进行操作。

针对特殊场景使用触发器可以带来一定的便利性；但不要过渡依赖触发器，避免造成数据库的性能下降和维护困难。接下来我们介绍触发器的管理操作。

MySQL 支持的三种触发器

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

1) INSERT 触发器

在 INSERT 语句执行之前或之后响应的触发器。

使用 INSERT 触发器需要注意以下几点：

在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

2) UPDATE 触发器

在 UPDATE 语句执行之前或之后响应的触发器。

使用 UPDATE 触发器需要注意以下几点：

在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
OLD 中的值全部是只读的，不能被更新。

注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。

3) DELETE 触发器

在 DELETE 语句执行之前或之后响应的触发器。

使用 DELETE 触发器需要注意以下几点：

在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
OLD 中的值全部是只读的，不能被更新。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

创建触发器

MySQL 使用CREATE TRIGGRT语句创建触发器，基本语法如下：

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

登录后复制

其中，trigger_name 是触发器的名称；BEFORE 和 AFTER 用于指定触发器的触发时机；INSERT、UPDATE 和 DELETE 用于定义触发事件的类型；table_name 是触发器关联的表名，不能是临时表或者视图；FOR EACH ROW 表明这是一个行级触发器；trigger_body 是触发器执行的具体语句。

举例来说，由于员工的薪水属于重要信息，所以需要记录薪水的修改历史。首先，我们创建一个审计表：

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

登录后复制

其中，audit_id 是自增主键；emp_id 是员工编号；old_salary 和 new_salary 分别用于存储修改前和修改后的月薪；change_date 记录了修改时间；change_by 记录了执行修改操作的用户。

然后创建一个触发器 tri_audit_salary，用于记录员工月薪的修改记录：

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

登录后复制

其中，DELIMITER 用于修改 SQL 语句的结束符，我们在前文介绍存储过程时已经有所了解；AFTER 表示在修改数据之后执行触发器；UPDATE 表示只针对更新操作记录数据变化；触发器主体中的 NEW 和 OLD 是 MySQL 触发器中的特殊变量，包含了修改后和修改前的记录，对于 INSERT 触发器而言没有 OLD 变量，对于 DELETE 触发器而言没有 NEW 变量；CURRENT_TIMESTAMP 和 USER() 都是 MySQL 系统函数，返回当前时间和登录的用户。

创建触发器之后，我们执行一些数据修改的操作，验证该触发器的效果：

UPDATE employee
SET email = &#39;sunqian@shuguo.net&#39;
WHERE emp_name = &#39;孙乾&#39;;

UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = &#39;孙乾&#39;;

SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

登录后复制

第一个 UPDATE 语句只修改了“孙乾”的电子邮箱，所以不会触发 tri_audit_salary；第二个 UPDATE 语句修改了他的月薪，触发了 tri_audit_salary。因此审计表 salary_audit 中包含一条数据，记录了月薪变化前后的情况。

如果想要同时审计新增员工和删除员工的操作，可以再创建一个 INSERT 触发器和 DELETE 触发器。

除此之外，MySQL 支持针对相同的触发时机和相同的事件定义多个触发器，同时指定它们的执行顺序：

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

登录后复制

其中，FOLLOWS 表示该触发器在触发器 other_trigger 之后执行；PRECEDES 表示该触发器在 other_trigger 之前执行；如果没有指定任何选项，默认情况下按照触发器的创建顺序执行。

查看触发器

使用SHOW TRIGGERS语句可以查看数据库中的触发器列表：

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE &#39;pattern&#39; | WHERE expr]

登录后复制

其中，db_name 用于查看指定数据库中的触发器，默认为当前数据库；LIKE 用于匹配存储过程的名称，WHERE 可以指定更多的过滤条件。例如，以下语句返回了当前数据库中的触发器：

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

登录后复制

另外，MySQL 系统表 INFORMATION_SCHEMA.TRIGGERS 中包含了更详细的触发器信息。

如果想要获取创建某个触发器的 DDL 语句，可以SHOW CREATE TRIGGER语句。例如：

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

登录后复制

删除触发器

MySQL 没有提供修改触发器的语句，只能通过DROP TRIGGER语句删除并再次创建触发器。例如，以下语句可以用于删除触发器 tri_audit_salary：

DROP TRIGGER IF EXISTS tri_audit_salary;

登录后复制

IF EXISTS 可以避免触发器 tri_audit_salary 不存在时产生错误。

【相关推荐：mysql视频教程】

以上是mysql的触发器是什么级的的详细内容。更多信息请关注PHP中文网其他相关文章！