• Tags ,         
  • 2018-10-12  12:09:36        
  • 7 °C    

    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

     
    转载请保留页面地址:https://www.breakyizhan.com/sql/6307.html