(16) MySQL 超新手入门之触发器

x33g5p2x  于2021-03-14 发布在 其他  
字(3.1k)|赞(0)|评价(0)|浏览(77)

1 Triggers的应用

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

字段名称型态NULL索引默认值其它资讯说明
lognobigint(20)NOPRINULLauto_increment纪录编号
logdttimestampNOCURRENT_TIMESTAMP日期时间
messagevarchar(64)YESNULL讯息

要完成这样的需求,每一次修改“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种类OLDNEW
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_SCHEMAvarchar(64)数据库
TRIGGER_NAMEvarchar(64)名称
EVENT_MANIPULATIONvarchar(6)启动的事件,有INSERT、UPDATE与DELETE
EVENT_OBJECT_SCHEMAvarchar(64)作用的数据库
EVENT_OBJECT_TABLEvarchar(64)作用的表格
ACTION_STATEMENTlongtext执行的工作
ACTION_TIMINGvarchar(6)启动的时机,有BEFORE与AFTER

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

mysql_16_snap_23

相关文章