暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

深入浅出MySQL-开发篇-触发器

DBA天团 2021-02-05
833


MySQL从5.0.2版本开始支持触发器的功能。触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。本章将详细介绍MySQL中触发器的使用方法。


13.1创建触发器


创建触发器的语法如下:


CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt


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


其中trigger_time是触发器的触发时间,可以是BEFORE或者AFTER,BEFORE的含义指在检查约束前触发,而AFTER是在检查约束后触发。

而trigger_event就是触发器的触发事件,可以是INSERT、UPDATE或者DELETE。

对同一个表相同触发时间的相同触发事件,只能定义一个触发器。例如,对某个表的不同字段的AFTER更新触发器,在使用Oracle数据库的时候,可以定义成两个不同的UPDATE触发器,更新不同的字段时触发单独的触发器,但是在MYSQL数据库中,只能定义成一个触发器,在触发器中通过判断更新的字段进行对应的处理。

使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发的,不支持语句级触发。

在样例数据库中,为film表创建了AFTER INSERT的触发器,具体如下:


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;

$$

delimiter ;


插入film表记录的时候,会向film_text表中也插入相应的记录。


mysql> INSERT INTO film VALUES

    -> (1001,'ACADEMY DINOSAUR',

    -> 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies',

    -> 2006,1,NULL,6,'0.99',86,'20.99','PG','Deleted Scenes,Behind the Scenes', '2006-02-15 05:03:42');

Query OK, 1 row affected (0.05 sec)

mysql> select * from film_text where film_id=1001 \G

*************************** 1. row ***************************

    film_id: 1001

      title: ACADEMY DINOSAUR

description: A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies

1 row in set (0.00 sec)


对于INSERT INTO…ON DUPLICATE KEY UPDATE…语句来说,触发触发器的顺序可能会造成疑惑。下面对film表分别创建了BEFROE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE触发器,然后插入记录,观察触发器的触发情况:


--创建BEFROE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE触发器:

mysql> create table tri_demo(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id));

Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_film_bef

    -> BEFORE INSERT ON film FOR EACH ROW BEGIN

    ->     INSERT INTO tri_demo (note) VALUES ('before insert');

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TRIGGER ins_film_aft

    -> AFTER INSERT ON film FOR EACH ROW BEGIN

    ->     INSERT INTO tri_demo (note) VALUES ('after insert');

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TRIGGER upd_film_bef

    -> BEFORE update ON film FOR EACH ROW BEGIN

    ->     INSERT INTO tri_demo (note) VALUES ('before update');

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TRIGGER upd_film_aft

    -> AFTER update ON film FOR EACH ROW BEGIN

    ->     INSERT INTO tri_demo (note) VALUES ('after update');

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)


--插入记录已经存在的情况:

mysql> select film_id,title from film where film_id = 1001;

+---------+------------------+

| film_id | title               |

+---------+------------------+

| 1001     | ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)


mysql> INSERT INTO film VALUES

    -> (1001,'Only test',

    -> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',

    -> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')

    -> ON DUPLICATE KEY

    -> UPDATE title='update record';

Query OK, 2 rows affected (0.05 sec)


mysql> select * from tri_demo;

+----+---------------+

| id | note            |

+----+---------------+

| 1  | before insert |

| 2  | before update |

| 3  | after update  |

+----+---------------+

3 rows in set (0.00 sec)


--插入新记录的情况:

mysql> delete from tri_demo;

Query OK, 3 rows affected (0.00 sec)

mysql> select film_id,title from film where film_id = 1002;

Empty set (0.00 sec)

mysql> INSERT INTO film VALUES

    -> (1002,'Only test',

    -> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',

    -> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')

    -> ON DUPLICATE KEY

    -> UPDATE title='update record';

Query OK, 1 row affected (0.05 sec)

mysql>

mysql> select * from tri_demo;

+----+---------------+

| id | note            |

+----+---------------+

| 4  | before insert |

| 5  | after insert   |

+----+---------------+

2 rows in set (0.00 sec)


从上面的例子可以知道,对于有重复记录,需要进行UPDATE操作的INSERT,触发器触发的顺序是BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE;对于没有重复记录的INSERT,就是简单地执行INSERT操作,触发器触发的顺序是BEFORE INSERT、AFTER INSERT。对于那些实际执行UPDATE操作的记录,仍然会执行BEFORE INSERT触发器的内容,在设计触发器的时候一定要考虑这种情况,避免错误地触发了触发器。


13.2删除触发器


一次可以删除一个触发程序,如果没有指定schema_name,默认为当前数据库,具体语法如下:


DROP TRIGGER [schema_name.]trigger_name


例如,要删除film表上的触发器ins_film,可以使用以下命令:


mysql> drop trigger ins_film;

Query OK, 0 rows affected (0.00 sec)



13.3查看触发器


可以通过执行SHOW TRIGGERS命令查看触发器的状态、语法等信息,但是因为不能查询指定的触发器,所以每次都返回所有的触发器的信息,使用起来不是很方便,具体语法如下


mysql> show triggers \G

*************************** 1. row ***************************

  Trigger: customer_create_date

    Event: INSERT

    Table: customer

Statement: SET NEW.create_date = NOW()

   Timing: BEFORE

  Created: NULL

 sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

  Definer: root@localhost

*************************** 2. row ***************************

……


另外一个查看方式是查询系统表的information_schema.triggers表,这个方式可以查询指定触发器的指定信息,操作起来明显方便很多:


mysql> desc triggers;

+---------------------------+--------------+------+----+--

| Field                          | Type           | Null | Key | Default | Extra |

+---------------------------+--------------+------+----+--

| TRIGGER_CATALOG             | varchar(512)   | YES  |     |           |       |

| TRIGGER_SCHEMA              | varchar(64)    | NO   |     |           |       |

| TRIGGER_NAME                | varchar(64)   | NO   |     |           |       |

| EVENT_MANIPULATION         | varchar(6)    | NO   |     |           |       |

| EVENT_OBJECT_CATALOG       | varchar(512) | YES  |     |           |       |

| EVENT_OBJECT_SCHEMA        | varchar(64)  | NO   |     |           |       |

| EVENT_OBJECT_TABLE         | varchar(64)  | NO   |     |           |       |

| ACTION_ORDER                | bigint(4)    | NO   |     | 0         |       |

| ACTION_CONDITION             | longtext     | YES  |     |           |       |

| ACTION_STATEMENT             | longtext     | NO   |     |           |       |

| ACTION_ORIENTATION           | varchar(9)   | NO   |     |           |       |

| ACTION_TIMING                  | varchar(6)   | NO   |     |           |       |

| ACTION_REFERENCE_OLD_TABLE | varchar(64)  | YES  |     |           |       |

| ACTION_REFERENCE_NEW_TABLE | varchar(64)  | YES  |     |           |       |

| ACTION_REFERENCE_OLD_ROW   | varchar(3)    | NO   |     |           |       |

| ACTION_REFERENCE_NEW_ROW   | varchar(3)   | NO   |     |           |       |

| CREATED                       | datetime     | YES  |     |           |       |

| SQL_MODE                      | longtext     | NO   |     |           |       |

| DEFINER                        | longtext     | NO   |     |           |       |

+----------------------------+-------------+------+----+--

19 rows in set (0.00 sec)


mysql> select * from triggers where trigger_name = 'ins_film_bef' \G

*************************** 1. row ***************************

           TRIGGER_CATALOG: NULL

            TRIGGER_SCHEMA: sakila

              TRIGGER_NAME: ins_film_bef

        EVENT_MANIPULATION: INSERT

      EVENT_OBJECT_CATALOG: NULL

       EVENT_OBJECT_SCHEMA: sakila

        EVENT_OBJECT_TABLE: film

              ACTION_ORDER: 0

          ACTION_CONDITION: NULL

          ACTION_STATEMENT: BEGIN

    INSERT INTO tri_demo (note) VALUES ('before insert');

END

        ACTION_ORIENTATION: ROW

             ACTION_TIMING: BEFORE

ACTION_REFERENCE_OLD_TABLE: NULL

ACTION_REFERENCE_NEW_TABLE: NULL

  ACTION_REFERENCE_OLD_ROW: OLD

  ACTION_REFERENCE_NEW_ROW: NEW

                   CREATED: NULL

                  SQL_MODE:

                   DEFINER: root@localhost

1 row in set (0.01 sec)


13.4触发器的使用


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

    触发程序不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL语句,但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。

    不能在触发器中使用以显式或隐式方式开始或结束事务的语句,如START TRANS- ACTION、COMMIT或ROLLBACK。

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


13.5小结


本节主要介绍了触发器的定义、修改以及触发器使用的一些注意事项。需要注意的是触发器是行触发的,每次增加、修改或者删除记录都会触发进行处理,编写过于复杂的触发器或者增加过多的触发器对记录的插入、更新、删除操作肯定会有比较严重的影响,因此数据库设计的时候要有所考虑,不要将应用的处理逻辑过多的依赖于触发器来处理。


深入浅出Mysql历史文章目录

MySQL基础篇-支持的数据类型

MySQL基础篇-支持的运算符号

MySQL基础篇-常用基本函数

关注网易乐得DBA

精深数据库神功



文章转载自DBA天团,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论