MySQL入门 (十五) : Triggers

1 Triggers的应用

在「cmdev」资料中有一个「emplog」表格,如果有人执行任何修改「cmdev.emp」表格资料的动作,都要新增一笔讯息到「cmdev.emplog」表格中,查询这个表格的资料,就可以知道在什么时候曾经修改过「cmdev.emp」表格中的资料:

栏位名称 型态 NULL 索引 预设值 其它资讯 说明
logno bigint(20) NO PRI NULL auto_increment 纪录编号
logdt timestamp NO CURRENT_TIMESTAMP 日期时间
message varchar(64) YES NULL 讯息

要完成这样的需求,每一次修改「cmdev.emp」表格资料时,你都必需执行下列的工作:

mysql_16_snap_01

要完成这样的需求,你可以使用stored routines来处理修改与新增纪录的工作,或是在应用程式中撰写程式来解决。 不过都会是一件很麻烦的事情,而且比较容易造成遗漏纪录的情况。

MySQL资料库提供一种特别的资料库元件,称为「triggers」,一般会把它称为「触发器」。 Triggers可以让你先把一些在特定状况要执行的叙述储存起来,MySQL资料库会在正确的时机自动帮你执行这些叙述:

mysql_16_snap_02

以上列讨论的需求来说,每一次修改「cmdev.emp」表格资料,都必须新增一笔纪录到「cmdev.emplog」表格中。 这 个需求的主角是「cmdev.emp」表格,所以你可以为这个表格建立一个trigger元件;因为是在修改资料的情况时才需要执行特定的工作,所以你要 选择「UPDATE trigger」;新增一笔纪录到「cmdev.emplog」表格中的叙述就是储存在「cmdev.emp」表格的「UPDATE trigger」中。

如果你建立好需要的trigger元件后,MySQL资料库就会自动帮你执行这些工作:

mysql_16_snap_03

2 建立Triggers

下列是建立trigger元件的语法:

mysql_16_snap_04

如果trigger元件执行的工作比较复杂,需要一个以上的叙述时,就要把叙述放在「BEGIN」与「END」区块中:

mysql_16_snap_05

你可以依照需求为一个表格建立不同的trigger元件:

mysql_16_snap_06

以上列讨论的需求来说,每一次修改「cmdev.emp」表格资料,都必须新增一笔纪录到「cmdev.emplog」表格中。 你要为「cmdev.emp」表格建立一个「UPDATE TRIGGER」;而「BEFORE」与「AFTER」就是「之前」与「之后」的意思。 如果建立「BEFORE UPDATE TRIGGER」,那就表示在修改资料前会执行trigger;如果建立「AFTER UPDATE TRIGGER」,那就表示在修改资料后会执行trigger。 以这个需求来说,「BEFORE」或「AFTER」都是一样的。

建立trigger元件与建立stored routines的方式一样,你也要使用「SQL script」来执行建立trigger的工作。 下列的范例建立一个名称为「emp_before_update」的trigger元件:

mysql_16_snap_07

执行上列的叙述后,MySQL资料库会储存你建立的trigger元件,可是它并不像stored routines可以用来呼叫与执行;MySQL资料库会自动帮你执行这些储存在trigger中的叙述。

为「cmdev.emp」表格建立一个「BEFORE UPDATE TRIGGER」以后,只要发生修改「cmdev.emp」表格资料的情况,MySQL资料库会自动执行这个trigger中的叙述:

mysql_16_snap_08

不论是「UPDATE」或是其它两种Trigger元件,MySQL资料库都是以「纪录」来执行trigger。 以下列的范例来说,一个会修改三笔纪录的「UPDATE」叙述,MySQL资料库会执行trigger三次:

mysql_16_snap_09

如果在执行修改「cmdev.emp」表格叙述以后,实际上并没有修改任何纪录资料,那MySQL资料库也不会执行trigger:

mysql_16_snap_10

在你建立trigger元件时,要特别注意下列的限制:

  • 同一个资料库不可以有相同名称的Trigger
  • TEMPORARY表格与View不可以建立Trigger
  • 不可以使用「SELECT」叙述
  • 不可以使用「CALL」叙述
  • 不可以使用与交易(transactions)相关的叙述,包含「START TRANSACTION」、「COMMIT」与「ROLLBACK」

3 删除Triggers

你可以使用下列的语法删除不再需要的trigger元件:

mysql_16_snap_11

如果想要修改trigger元件中的叙述,你要先删除以后,再建立新的trigger元件。 所以你可以在在建立trigger元件的叙述中,加入删除trigger元件的叙述:

mysql_16_snap_12

4 OLD与NEW关键字

在triggers元件中,可以使用一般的SQL叙述完成需要执行的工作,也可以使用在stored routines中讨论过的变数与流程控制,让triggers元件可以处理比较复杂的需求。 MySQL资料库在triggers元件中额外提供「OLD」与「NEW」两个关键字:

mysql_16_snap_13

因为「OLD」与「NEW」两个关键字的特性,所以它们可以使用的triggers种类会有一些限制:

Trigger种类 OLD NEW
INSERT 不能使用 新增的栏位资料
UPDATE 修改前的栏位资料 修改后的栏位资料
DELETE 删除前的栏位资料 不能使用

以「cmdev.emp」表格的「UPDATE TRIGGER」来说,下列是使用「OLD」与「NEW」关键字取得的栏位值:

mysql_16_snap_14

延续上列为更新「cmdev.emp」表格执行纪录工作的trigger来说,如果想要让纪录的讯息更加详细,包含修改前与修改后的部门编号:

mysql_16_snap_15

要完成上列的需求,就必须使用「OLD」与「NEW」关键字取得的栏位值:

mysql_16_snap_16

为表格建立「UPDATE TRIGGER」以后,就表示执行这个表格的修改动作,都会执行这个trigger元件:

mysql_16_snap_17

如果要将「emp_before_update」的需求,修改为「只有在修改员工的部门编号时,才需要新增修改纪录」,你就可以使用在sotred routines讨论过的「IF」指令来完成这个需求:

mysql_16_snap_18

在「INSERT TRIGGER」中使用「NEW」关键字时,要特别注意「AUTO_INCREMENT」栏位型态:

mysql_16_snap_19

如果有需要的话,你也可以使用「SET」叙述设定「NEW」关键字指定的栏位值。 以下列的情况来说:

mysql_16_snap_20

要解决上列的问题,你可以要求在新增资料的时候,不要使用小写的文字。 不过使用下列的「BEFORE INSERT TRIGGER」来处理的话,会更方便一些:

mysql_16_snap_21

建立好这个「BEFORE INSERT TRIGGER」以后,就算新增的员工资料包含小写的名称与职务,这个trigger元件都会在新增纪录之前,把它们转换为大写:

mysql_16_snap_22

5 查询Triggers的相关资讯

如果想要查询triggers的相关资讯,可以查询「information_schema.TRIGGERS」表格,下列是它的主要栏位:

栏位名称 型态 说明
TRIGGER_SCHEMA varchar(64) 资料库
TRIGGER_NAME varchar(64) 名称
EVENT_MANIPULATION varchar(6) 启动的事件,有INSERT、UPDATE与DELETE
EVENT_OBJECT_SCHEMA varchar(64) 作用的资料库
EVENT_OBJECT_TABLE varchar(64) 作用的表格
ACTION_STATEMENT longtext 执行的工作
ACTION_TIMING varchar(6) 启动的时机,有BEFORE与AFTER

如果你想要查询建立某个stored routines的详细资讯,可以使用下列的语法:

mysql_16_snap_23

 

翻译自: http://www.codedata.com.tw/database/mysql-tutorial-16-triggers/

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (九) : 子查询 Subquery

MySQL入门 (十) : Views

MySQL入门 (十一) : Prepared Statements

MySQL入门 (十二) : Stored Routines 入门

MySQL入门 (十三) : Stored Routines 的变数与流程

MySQL入门 (十四) : Stored Routines 进阶

MySQL入门 (十六) : 查询information_schema

 

本文: MySQL入门 (十五) : Triggers