MySQL 触发器

前言

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

1、创建触发器

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    # 创建触发器,单条执行语句

    # 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)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    # 创建触发器,多条执行语句

    # 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
  • 4)示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    > 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,默认为当前数据库。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 删除触发器
    # 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 命令查看所有触发器的状态、语法等信息。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    # 查看所有的触发器
    > 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 表的方式查询指定触发器的指定信息。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    > 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)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    # 查询指定触发器
    > 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 触发器的内容,在设计触发器的时候一定要考虑这种情况,避免错误地触发了触发器。
文章目录
  1. 1. 前言
  2. 2. 1、创建触发器
  3. 3. 2、删除触发器
  4. 4. 3、查看触发器
  5. 5. 4、使用触发器
隐藏目录