触发器

摘自书本

第13章触发器

　　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 INOT…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_ZER

O,TRADITIONAL,NO_AUTO_CREATE_USER

Definer: root@localhost

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

……

另外一个查看方式是查询系统表的 information_schema.triggers 表，这个方式可以查询指定

触发器的指定信息，操作起来明显方便很多：

mysql> desc triggers;

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

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

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

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

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 TRANSACTION、

COMMIT 或 ROLLBACK。

MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一

步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为

一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无

法回滚，这也是设计触发器的时候需要注意的问题。

13.5 小结

本节主要介绍了触发器的定义、修改以及触发器使用的一些注意事项。需要注意的是触发器

是行触发的，每次增加、修改或者删除记录都会触发进行处理，编写过于复杂的触发器或者

增加过多的触发器对记录的插入、更新、删除操作肯定会有比较严重的影响，因此数据库设

计的时候要有所考虑，不要将应用的处理逻辑过多的依赖于触发器来处理。

posted @ 2020-12-02 16:27 wq9 阅读(127) 评论(0) 收藏举报

刷新页面返回顶部

wq9

触发器

公告