mysql从头学一 1.1触发器

Q：什么是触发器？

A：

　　触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

触发器的特性：

　　1、有begin end体，begin end;之间的语句可以写的简单或者复杂

　　2、什么条件会触发：I、D、U

　　3、什么时候触发：在增删改前或者后

　　4、触发频率：针对每一行执行

　　5、触发器定义在表上，附着在表上。

也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数据库端确保数据的完整性。

注意：cannot associate a trigger with a TEMPORARY table or a view.

！！尽量少使用触发器，不建议使用。

　　假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

　　触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。 

创建触发器
创建触发器的语法如下：
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, 
166
触发器，然后插入记录，观察触发器的触发情况：
--创建 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',
167
 -> '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。对于那些
168
实际执行 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;
+----------------------------+--------------+------+-----+---------+-------+
| 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 | | | |
169
| 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)
170
13.4 触发器的使用
触发器执行的语句有以下两个限制。
 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用 CALL 语句的动态 SQL
语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT
或者 INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、
COMMIT 或 ROLLBACK。
MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一
步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为
一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无
法回滚，这也是设计触发器的时候需要注意的问题