August 29, 2016

MySQL入门 (十五) : Triggers

By Gideon Phone / IOS / Objective-C / Swift 1 Comment

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」表格资料时，你都必需执行下列的工作：

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

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

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

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

2 建立Triggers

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

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

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

以上列讨论的需求来说，每一次修改「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资料库会储存你建立的trigger元件，可是它并不像stored routines可以用来呼叫与执行；MySQL资料库会自动帮你执行这些储存在trigger中的叙述。

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

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

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

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

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

3 删除Triggers

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

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

4 OLD与NEW关键字

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

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

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

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

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

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

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

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

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

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

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

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

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