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
如果你想要查询建立某个stored routines的详细资讯,可以使用下列的语法: