一、触发器概述
  • 触发器是与特定表相关联的存储过程，其定义会在执行表的INSERT、DELETE或UPDATE语句时，被自动激活。触发器可被设置成语句处理各行之前或之后激活。触发器的定义包含有一条会在触发器被激活时执行的语句。
  • 只有表才支持触发器，视图不支持（临时表也支持）。

触发器的类型

  • INSERT型触发器：插入数据时触发（通过insert、load data、replace）。
  • UPDATE型触发器：更新数据时触发（通过update）。
  • DELETE型触发器：删除数据时触发（通过delete、replace）。
  • 其他MySQL语句不支持触发器。

触发器的好处

  • 触发器可以检査或修改将被插入或用来更新行的那些新的数据值。这意味着我们可以利用触发器来实现数据完整性约束，如检査某个百分比数值是不是落在了0~100这样一个区间内。触发器还可以用来对输入数据进行过滤。
  • 触发器可以基于某个表达式来为列提供默认值，甚至可以为那些只能使用常量默认值进行定义的列类型提供值。
  • 触发器可以在行删除或更新之前先检査行的当前内容。这种能力能完成许多任务，如记录已有行的更改情况。

保证每个数据库的触发器名唯一

  • 触发器名在一个表中必须唯一，但是在每个数据库中可以重复，于是同一数据库中的两个表可具有相同名称的触发器。
  • 但是，最好在数据库范围内使用唯一的触发器名。

OLD、NEW关键字

  • 在触发器程序体中，old代表插入、更新、删除表时，表的源数据。
  • new代表插入、更新、删除表时，表的新数据。

触发器失败

  • 如果before触发器失败，则MySQL将不执行请求的操作。此外，如果before触发器或语句本身失败，MySQL将不执行after触发器。
二、触发器的创建

语法格式

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt;
  • trigger_name：标识触发器名称，用户自行指定。
  • trigger_time：标识触发时机，取值为BEFORE或AFTER。
  • trigger_event：标识触发事件，取值为 INSERT、UPDATE或 DELETE。
  • tbl_name：标识建立触发器的表名，即在哪张表上建立触发器。
  • trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
  • for each row：MySQL支持FOR EACH ROW的触发方式，即按每行记录进行触发，不支持像DB2的FOR EACH STATEMENT的触发方式。

演示案例

  • 第一步：建立一个表并添加数据。
create table text_tb(
    number int not null
    id int not null
);

MySQL trigger触发器_数据

  • 第二步：建立一个update型触发器，触发器名为upd_tb（更新数据之前，判断更新的number的数值范围，然后进行修改）。
limiter //
create trigger upd_tb before update on text_tb
for each row
begin
  if new.number <0 then set new.number=0;
  elseif new.number >100 then set new.number=100;
  end if;
end;
//
  • 第三步：更新表的数据，并查看触发器的功能十分生效。

MySQL trigger触发器_trigger_02

MySQL trigger触发器_其他_03

五、触发器的查看
  • 在MySQL中，所有的触发器都定义在information_schema数据库的triggers表中。例如：

MySQL trigger触发器_触发器_04

  • 通过show triggers，但是此命令会返回所有触发器的信息。例如：

MySQL trigger触发器_数据_05

六、删除触发器 
DROP TRIGGER [IF EXISTS] [schema_name].trigger_name;

MySQL trigger触发器_其他_06

七、触发器的审计功能
  • 概念：使用触发器对表数据进行记录、跟踪。

演示案例

  • 第一步：建立一个表（用于记录数据，相当于一个日志表）。

MySQL trigger触发器_数据库_07

  • 第二步：创建触发器（每当我们向text_tb表中插入数据时，review表就会插入一条新数据）。我们这里使用insert，delete与update的都是相同的原理。

MySQL trigger触发器_数据_08

  • 第三步：向text_tb表中插入数据，并且查看review表是否有记录。

MySQL trigger触发器_trigger_09

八、触发器的限制

待续