MySQL 触发器

前言

  • 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

1、创建触发器

  • 1)触发器只能创建在永久表(Permanent Table)上,不能对临时表(Temporary Table)创建触发器。

    # 创建触发器,单条执行语句
    
    # create trigger
        触发器名                // 不能与已存在的触发器重复
        触发时间                // before、after,表示事件之前或之后触发
        触发事件                // insert、update、delete 触发该触发器的具体事件
      on 
        触发器响应表名           // 该触发器操作的表
      for each row
        触发操作;
    
    > create trigger
        trigger_student_count_insert
        after 
        insert
      on
        student
      for each row
        update student_count set scount = scount + 1;
    
        Query OK, 0 rows affected (0.09 sec)
    # 创建触发器,多条执行语句
    
    # delimiter $$             // 定义结束符
      create trigger
        触发器名                // 不能与已存在的触发器重复
        触发时间                // before、after,表示事件之前或之后触发
        触发事件                // insert、update、delete 触发该触发器的具体事件
      on 
        触发器响应表名           // 该触发器操作的表
      for each row
      begin
        触发操作(多条执行语句);   // 每条语句使用分号结束
      end
      $$                       // 使用自定义结束符
    
    > delimiter $$
    > create trigger
        trigger_student_count_insert
        after 
        insert
      on
        student
      for each row
      begin
        update student_count set scount = scount + 1;
        update student_count set scount = scount + 1000;
      end
      $$
    
        Query OK, 0 rows affected (0.10 sec)
  • 2)对同一个表相同触发时间的相同触发事件,只能定义一个触发器。

    • 例如,对某个表的不同字段的 after 更新触发器,在使用 Oracle 数据库的时候,可以定义成两个不同的 update 触发器,更新不同的字段时触发单独的触发器,但是在 MYSQL 数据库中,只能定义成一个触发器,在触发器中通过判断更新的字段进行对应的处理。
  • 3)使用别名 old 和 new 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。

    别名 解释
    new 在触发器为 insert 事件类型时有效,表示当前正在插入的数据,如 new.class
    old 在触发器为 delete 事件类型时有效,表示当前正在删除的数据,如 old.class
    > delimiter $$
      create trigger 
        ins_film
        after 
        insert 
      on 
        film 
      for each row 
      begin
        insert into 
          film_text (film_id, title, description)
          values (new.film_id, new.title, new.description);
      end;
      $$
    • 插入 film 表记录的时候,会向 film_text 表中也插入相应的记录。

2、删除触发器

  • 一次可以删除一个触发程序,如果没有指定 schema_name,默认为当前数据库。

    # 删除触发器
    # drop trigger [schema_name.]触发器名;
    > drop trigger trigger_student_count_insert;
    
        Query OK, 0 rows affected (0.00 sec)
    
    # 删除触发器,如果触发器存在就删除
    # drop trigger if exists 触发器名;
    > drop trigger if exists trigger_student_count_insert;

3、查看触发器

  • 可以通过执行 show triggers 命令查看所有触发器的状态、语法等信息。

    # 查看所有的触发器
    > show triggers;
    
        *************************** 1. row ***************************
                     Trigger: trigger_student_count_insert
                       Event: INSERT
                       Table: student
                   Statement: begin
                                update student_count set scount = scount + 1;
                                update student_count set scount = scount + 1000;
                              end
                      Timing: AFTER
                     Created: 2018-06-28 18:42:15.47
                    sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
                              NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                     Definer: root@localhost
        character_set_client: utf8mb4
        collation_connection: utf8mb4_0900_ai_ci
          Database Collation: gbk_chinese_ci
    
        *************************** 2. row ***************************
        ...
    
        ... row in set (0.01 sec)
  • 可以通过查询系统表的 information_schema.triggers 表的方式查询指定触发器的指定信息。

    > use information_schema;
    
        Reading table information for completion of table and column names
        You can turn off this feature to get a quicker startup with -A
    
        Database changed
    
    > desc triggers;
    
        +----------------------------+----------------------------------+------+-----+----------------------+--------------------------------+
        | Field                      | Type                             | Null | Key | Default              | Extra                          |
        +----------------------------+----------------------------------+------+-----+----------------------+--------------------------------+
        | TRIGGER_CATALOG            | varchar(64)                      | NO   |     | NULL                 |                                |
        | TRIGGER_SCHEMA             | varchar(64)                      | NO   |     | NULL                 |                                |
        | TRIGGER_NAME               | varchar(64)                      | NO   |     | NULL                 |                                |
        | EVENT_MANIPULATION         | enum('INSERT','UPDATE','DELETE') | NO   |     | NULL                 |                                |
        | EVENT_OBJECT_CATALOG       | varchar(64)                      | NO   |     | NULL                 |                                |
        | EVENT_OBJECT_SCHEMA        | varchar(64)                      | NO   |     | NULL                 |                                |
        | EVENT_OBJECT_TABLE         | varchar(64)                      | NO   |     | NULL                 |                                |
        | ACTION_ORDER               | int(10) unsigned                 | NO   |     | NULL                 |                                |
        | ACTION_CONDITION           | binary(0)                        | YES  |     | NULL                 |                                |
        | ACTION_STATEMENT           | longtext                         | NO   |     | NULL                 |                                |
        | ACTION_ORIENTATION         | varchar(3)                       | NO   |     |                      |                                |
        | ACTION_TIMING              | enum('BEFORE','AFTER')           | NO   |     | NULL                 |                                |
        | ACTION_REFERENCE_OLD_TABLE | binary(0)                        | YES  |     | NULL                 |                                |
        | ACTION_REFERENCE_NEW_TABLE | binary(0)                        | YES  |     | NULL                 |                                |
        | ACTION_REFERENCE_OLD_ROW   | varchar(3)                       | NO   |     |                      |                                |
        | ACTION_REFERENCE_NEW_ROW   | varchar(3)                       | NO   |     |                      |                                |
        | CREATED                    | timestamp(2)                     | NO   |     | CURRENT_TIMESTAMP(2) | on update CURRENT_TIMESTAMP(2) |
        | SQL_MODE                   | set('REAL_AS_FLOAT',
                                        'PIPES_AS_CONCAT',
                                        'ANSI_QUOTES',
                                        'IGNORE_SPACE',
                                        'NOT_USED',
                                        'ONLY_FULL_GROUP_BY',
                                        'NO_UNSIGNED_SUBTRACTION',
                                        'NO_DIR_IN_CREATE',
                                        'NOT_USED_9',
                                        'NOT_USED_10',
                                        'NOT_USED_11',
                                        'NOT_USED_12',
                                        'NOT_USED_13',
                                        'NOT_USED_14',
                                        'NOT_USED_15',
                                        'NOT_USED_16',
                                        'NOT_USED_17',
                                        'NOT_USED_18',
                                        'ANSI',
                                        'NO_AUTO_VALUE_ON_ZERO',
                                        'NO_BACKSLASH_ESCAPES',
                                        'STRICT_TRANS_TABLES',
                                        'STRICT_ALL_TABLES',
                                        'NO_ZERO_IN_DATE',
                                        'NO_ZERO_DATE',
                                        'INVALID_DATES',
                                        'ERROR_FOR_DIVISION_BY_ZERO',
                                        'TRADITIONAL',
                                        'NOT_USED_29',
                                        'HIGH_NOT_PRECEDENCE',
                                        'NO_ENGINE_SUBSTITUTION',
                                        'PAD_CHAR_TO_FULL_LENGTH')   | NO   |     | NULL                 |                                |
        | DEFINER                    | varchar(93)                      | NO   |     | NULL                 |                                |
        | CHARACTER_SET_CLIENT       | varchar(64)                      | NO   |     | NULL                 |                                |
        | COLLATION_CONNECTION       | varchar(64)                      | NO   |     | NULL                 |                                |
        | DATABASE_COLLATION         | varchar(64)                      | NO   |     | NULL                 |                                |
        +----------------------------+----------------------------------+------+-----+----------------------+--------------------------------+
        22 rows in set (0.00 sec)
    # 查询指定触发器
    > select * from triggers where trigger_name = 'trigger_student_count_insert' \G
    
        *************************** 1. row ***************************
                   TRIGGER_CATALOG: def
                    TRIGGER_SCHEMA: test
                      TRIGGER_NAME: trigger_student_count_insert
                EVENT_MANIPULATION: INSERT
              EVENT_OBJECT_CATALOG: def
               EVENT_OBJECT_SCHEMA: test
                EVENT_OBJECT_TABLE: student
                      ACTION_ORDER: 1
                  ACTION_CONDITION: NULL
                  ACTION_STATEMENT: begin
                                      update student_count set scount = scount + 1;
                                      update student_count set scount = scount + 1000;
                                    end
                ACTION_ORIENTATION: ROW
                     ACTION_TIMING: AFTER
        ACTION_REFERENCE_OLD_TABLE: NULL
        ACTION_REFERENCE_NEW_TABLE: NULL
          ACTION_REFERENCE_OLD_ROW: OLD
          ACTION_REFERENCE_NEW_ROW: NEW
                           CREATED: 2018-06-28 18:42:15.47
                          SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                           DEFINER: root@localhost
              CHARACTER_SET_CLIENT: utf8mb4
              COLLATION_CONNECTION: utf8mb4_0900_ai_ci
                DATABASE_COLLATION: gbk_chinese_ci
        1 row in set (0.00 sec)

4、使用触发器

  • 触发器执行的语句有以下两个限制。

    • 触发程序不能调用将数据返回客户端的存储程序,也不能使用采用 call 语句的动态 SQL 语句,但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 out 或者 inout 类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。
    • 不能在触发器中使用以显式或隐式方式开始或结束事务的语句,如 start trans-action、commit 或 rollback。
  • 触发器触发顺序

    • MySQL 的触发器是按照 before 触发器、行操作、after 触发器的顺序执行的,其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作,那么会整个作为一个事务被回滚(Rollback),但是如果是对非事务表进行的操作,那么已经更新的记录将无法回滚,这也是设计触发器的时候需要注意的问题 。

    • 对于有重复记录、需要进行 update 操作的 insert,触发器触发的顺序是 before insert、before update、after update;
    • 对于没有重复记录的 insert,就是简单地执行 insert 操作,触发器触发的顺序是 before insert、after insert。
    • 对于那些实际执行 update 操作的记录,仍然会执行 before insert 触发器的内容,在设计触发器的时候一定要考虑这种情况,避免错误地触发了触发器。

posted @ 2018-06-28 19:23  QianChia  阅读(...)  评论(... 编辑 收藏