当前位置：首页 > news >正文

MySQL：触发器（Trigger）

news 来源：原创 2024/9/20 7:50:46

触发器（TRIGGER）是用户定义在关系表上的一类由事件驱动的特殊过程，在满足一定条件或达到一定阈值时会自动触发。可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

Trigger 又叫做 事件-条件-动作（event- condition-action）规则。当特定的系统事件（如对一个表的增、删、改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段 SQL 存储过程

触发器是 MySQL 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是 CALL 手动启动，而是由事件来触发，比如当对一个表进行操作（INSERT，DELETE， UPDATE）时就会激活它执行。

创建触发器

四要素

触发器创建的四个要素：

监视地点 TABLE
监视事件 INSERT / UPDATE / DELETE
触发时间 AFTER / BEFORE
触发事件 INSERT / UPDATE / DELETE

只有表的创建者才可以在表上创建触发器，并且一个表上只能创建有限数量的触发器

语法

触发器的语法会根据具体的数据库管理系统而有所不同，下面是一般常用的触发器语法：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[REFERNCEING NEW|OLD ROW|TABLE AS <var_name>]
FOR EACH [ROW | STATEMENT]
[WHEN condition]
BEGIN-- 触发器动作
END;

trigger_name 是触发器的名称，可以自定义。
{BEFORE | AFTER} 指定触发器在事件之前或之后执行。
- AFTER 操作，是在执行了监视动作后，才会执行触发事件
- BEFORE 操作，是在执行了监视动作前，会执行触发事件
{INSERT | UPDATE | DELETE} 指定触发器关联的事件类型。可以是INSERT、DELETE或UPDATE，也可以是它们的组合；同样也可以 UPDATE OF<列名,...,>，也即进一步指明哪些列变化时需要激活触发器
table_name 是触发器关联的表名。触发器只能定义在表上，不可在视图上。当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器
REFERENCING 用来指定在触发器程序中使用的伪表或变量的名称，以便访问新插入、更新或删除的行。
- OLD ROW 和 NEW ROW: 分别代表被修改的行之前和之后的值。这些值可以通过使用 OLD 和 NEW 前缀来访问到，例如在 AFTER UPDATE 触发器中，可以通过 OLD.column_name 和 NEW.column_name 分别访问修改之前和之后的列值。
- OLD TABLE 和 NEW TABLE: 用于 DELETE 或 INSERT 操作时访问伪表。Oracle 数据库中，这些伪表也称为临时表，它们具有与被触发的表相同的结构，但内容为更新前或更新后的值。在 MySQL 中使用 OLD TABLE 可以访问删除操作之前的表中的行，使用 NEW TABLE 可以访问插入操作之后的表中的行。
FOR EACH ROW 行级触发器：针对每一条记录执行触发器操作，当有大量行被修改时，FOR EACH ROW 触发器会执行相同数量次的操作。
FOR EACH STATEMENT 语句触发器：只执行一次触发体动作，即在触发器执行的条件下对整个 SQL 语句中受影响的所有行执行相同的操作。即使它会受到多行更新的影响，只会执行一次触发器操作
WHEN condition 是可选的，用于指定触发器执行的条件。如果省略WHEN 触发条件，则触发体动作在触发器激活后立即执行
BEGIN 和 END 之间是触发器的动作，可以包含 SQL 语句或调用其他存储过程、函数等。
触发体动作：触发动作体既可以是一个匿名 PL/SQL 过程块，也可以是对已创建存储过程的调用。
- 如果是行级触发器，用户可以在过程体中使用 NEW 和 OLD 引用 UPDATE/INSERT 事件之后的新值和 UPDATE/DELETE 事件之前的旧值
- 如果是语句级触发器，则不能在触发动作体中使用 NEW 或 OLD 进行引用。
- 如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化

示例

创建 INSERT 触发器

DELIMITER $$  
CREATE TRIGGER user_insert_trigger  
AFTER INSERT ON user  
FOR EACH ROW  
BEGIN  INSERT INTO user_operation(operation_type, operation_content)  VALUES('insert', CONCAT('添加后的数据:', NEW.name));  
END$$  
DELIMITER ;

这个触发器会在向 user 表插入数据后，向 user_operation 表中插入一条记录，记录操作类型为 INSERT 和插入的数据内容。

创建 UPDATE 触发器

DELIMITER $$  
CREATE TRIGGER user_update_trigger  
AFTER UPDATE ON user  
FOR EACH ROW  
BEGIN  INSERT INTO user_operation(operation_type, operation_content)  VALUES('update', CONCAT('修改前的数据: name:', OLD.name, ', age:', OLD.age));  INSERT INTO user_operation(operation_type, operation_content)  VALUES('update', CONCAT('修改后的数据: name:', NEW.name, ', age:', NEW.age));  
END$$  
DELIMITER ;