11.触发器

1.什么是触发器

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

2.触发器的操作

2.1创建触发器

创建语法

CREATE　TRIGGER　trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW tigger_stmt

-- trigger_time 触发器的触发时间，可以时BEFORE或者AFTER，BEFORE是在检查约束前触发，AFTER是在检查约束后触发。
-- trigger_event 触发器触发的触发事件，可以是INSERT、UPDATE、DELETE。对同一表相同触发事件的相同触发事件，只能定义一个触发器。

触发器只能创建在永久表上，不能创建在临时表上

为city创建 AFTER INSERT 触发器：

DELIMITER $$
create TRIGGER ins_city AFTER INSERT on city FOR EACH ROW 
BEGIN
	INSERT INTO	city_memory (city,country_id) VALUES(new.city,new.country_id);
END;
$$

DELIMITER ;

向city中插入一条数据,city_memory中也会自动插入这条数据.

INSERT INTO city (city,country_id) VALUES('Lanzhou',1);
--Query OK, 1 row affected (0.07 sec)
select * from city_memory where country_id = 1 \G;
--*************************** 1. row ***************************
--    city_id: 2
--       city: Lanzhou
-- country_id: 1
--last_update: 2020-08-31 22:06:28
--1 row in set (0.00 sec)

对于INSERT INTO … ON DUPLICATE KEY UPDATE … 语句来说,触发触发器的顺序可能会造成疑惑.下面对表city分别创建了BEFORE INSERT 、 AFTER　INSERT、BEFORE UPDATE 、AFTER　UPDATE触发器

CREATE TABLE tri_demo(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id));

CREATE TRIGGER ins_city_bef
BEFORE INSERT ON city FOR EACH ROW  BEGIN
	INSERT INTO tri_demo (note) VALUES('before insert');
END;
$$

CREATE TRIGGER ins_city_aft 
AFTER INSERT ON city FOR EACH ROW BEGIN
	INSERT INTO tri_demo (note) VALUES('after insert');
END;
$$

CREATE TRIGGER up_city_bef
BEFORE UPDATE ON city FOR EACH ROW BEGIN
 	INSERT INTO tri_demo (note) VALUES('before update');
END;
$$

CREATE TRIGGER up_city_aft
AFTER UPDATE ON city FOR EACH ROW BEGIN
	INSERT INTO tri_demo (note) VALUES('after update');
END;
$$

查看city表中已经存在的数据

select * from city;
--+---------+---------+------------+---------------------+
--| city_id | city    | country_id | last_update         |
--+---------+---------+------------+---------------------+
--|       1 | Beijing |          1 | 2020-08-20 15:26:58 |
--|       4 | Lanzhou |          1 | 2020-08-31 22:06:28 |
--+---------+---------+------------+---------------------+

• 插入记录已近存在的情况

INSERT INTO city VALUES(1,'ChengDu',1,null) ON DUPLICATE KEY UPDATE country_id = 01 ;
mysql> select * from tri_demo;
--+----+---------------+
--| id | note          |
--+----+---------------+
--|  1 | before insert |
--|  2 | before update |
--|  3 | after update  |
--+----+---------------+
--3 rows in set (0.00 sec)

• 插入新纪录的情况

delete from tri_demo;
--Query OK, 3 rows affected (0.07 sec)
INSERT INTO city VALUES(0,'ChongQin',1,null) ON DUPLICATE KEY UPDATE country_id =01;
--Query OK, 1 row affected (0.07 sec)
select * from tri_demo;
--+----+---------------+
| id | note          |
+----+---------------+
|  4 | before insert |
|  5 | after insert  |
+----+---------------+
2 rows in set (0.00 sec)

3.删除触发器

语法

DROP TRIGGER [scheme_name.]trigger_name;

例如要删除city表上的ins_city_bef触发器

DROP TRIGGER ins_city_bef;
--Query OK, 0 rows affected (0.07 sec)

4.查看触发器

4.1 SHOW TRIGGER命令

可以查看触发器的状态语法等信息,但是不能查看指定的触发器

show triggers \G;
--*************************** 1. row ***************************
--             Trigger: ins_city
--               Event: INSERT
--               Table: city
--           Statement: BEGIN
--        INSERT INTO     city_memory (city,country_id) VALUES(new.city,new.country_id);
--        END
--              Timing: AFTER
--             Created: 2020-08-31 22:02:52.13
--            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
--             Definer: root@localhost
--character_set_client: utf8mb4
--collation_connection: utf8mb4_general_ci
--  Database Collation: utf8_general_ci
--*************************** 2. row ***************************
--             Trigger: ins_city_aft
--               Event: INSERT
--               Table: city
--           Statement: BEGIN
--        INSERT INTO tri_demo (note) VALUES('after insert');
--END
--              Timing: AFTER
--             Created: 2020-08-31 22:52:50.82
--            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
--             Definer: root@localhost
--character_set_client: utf8
--collation_connection: utf8_general_ci
--  Database Collation: utf8_general_ci
--*************************** 3. row ***************************
--             Trigger: up_city_bef
--               Event: UPDATE
--               Table: city
--           Statement: BEGIN
--        INSERT INTO tri_demo (note) VALUES('before update');
--END
--              Timing: BEFORE
--             Created: 2020-08-31 22:52:50.89
--            sql_mode: -ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
--             Definer: root@localhost
--character_set_client: utf8
--collation_connection: utf8_general_ci
--  Database Collation: utf8_general_ci
--*************************** 4. row ***************************
--             Trigger: up_city_aft
--               Event: UPDATE
--               Table: city
--           Statement: BEGIN
--        INSERT INTO tri_demo (note) VALUES('after update');
--END
--              Timing: AFTER
--             Created: 2020-08-31 22:52:53.92
--            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
--             Definer: root@localhost
--character_set_client: utf8
--collation_connection: utf8_general_ci
--  Database Collation: utf8_general_ci
--4 rows in set (0.00 sec)

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

desc information_schema.triggers;
--+----------------------------+---------------+------+-----+---------+-------+
--| Field                      | Type          | Null | Key | Default | Extra |
--+----------------------------+---------------+------+-----+---------+-------+
--| TRIGGER_CATALOG            | varchar(512)  | NO   |     |         |       |
--| TRIGGER_SCHEMA             | varchar(64)   | NO   |     |         |       |
--| TRIGGER_NAME               | varchar(64)   | NO   |     |         |       |
--| EVENT_MANIPULATION         | varchar(6)    | NO   |     |         |       |
--| EVENT_OBJECT_CATALOG       | varchar(512)  | NO   |     |         |       |
--| EVENT_OBJECT_SCHEMA        | varchar(64)   | NO   |     |         |       |
--| EVENT_OBJECT_TABLE         | varchar(64)   | NO   |     |         |       |
--| ACTION_ORDER               | bigint(4)     | NO   |     | 0       |       |
--| ACTION_CONDITION           | longtext      | YES  |     | NULL    |       |
--| ACTION_STATEMENT           | longtext      | NO   |     | NULL    |       |
--| ACTION_ORIENTATION         | varchar(9)    | NO   |     |         |       |
--| ACTION_TIMING              | varchar(6)    | NO   |     |         |       |
--| ACTION_REFERENCE_OLD_TABLE | varchar(64)   | YES  |     | NULL    |       |
--| ACTION_REFERENCE_NEW_TABLE | varchar(64)   | YES  |     | NULL    |       |
--| ACTION_REFERENCE_OLD_ROW   | varchar(3)    | NO   |     |         |       |
--| ACTION_REFERENCE_NEW_ROW   | varchar(3)    | NO   |     |         |       |
--| CREATED                    | datetime(2)   | YES  |     | NULL    |       |
--| SQL_MODE                   | varchar(8192) | NO   |     |         |       |
--| DEFINER                    | varchar(93)   | NO   |     |         |       |
--| CHARACTER_SET_CLIENT       | varchar(32)   | NO   |     |         |       |
--| COLLATION_CONNECTION       | varchar(32)   | NO   |     |         |       |
--| DATABASE_COLLATION         | varchar(32)   | NO   |     |         |       |
--+----------------------------+---------------+------+-----+---------+-------+
--22 rows in set (0.00 sec)
select * from  information_schema.triggers where trigger_name = 'ins_city_bef' \G;
--*************************** 1. row ***************************
--           TRIGGER_CATALOG: def
--            TRIGGER_SCHEMA: test
--              TRIGGER_NAME: ins_city_aft
--        EVENT_MANIPULATION: INSERT
--      EVENT_OBJECT_CATALOG: def
--       EVENT_OBJECT_SCHEMA: test
--        EVENT_OBJECT_TABLE: city
--              ACTION_ORDER: 2
--          ACTION_CONDITION: NULL
--          ACTION_STATEMENT: BEGIN
--        INSERT INTO tri_demo (note) VALUES('after insert');
--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: 2020-08-31 22:52:50.82
--                  SQL_MODE: --ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN--GINE_SUBSTITUTION
--                   DEFINER: root@localhost
--      CHARACTER_SET_CLIENT: utf8
--      COLLATION_CONNECTION: utf8_general_ci
--        DATABASE_COLLATION: utf8_general_ci
--1 row in set (0.01 sec)

5.触发器的使用

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

• 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用 CALL 语句的动态 SQL语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT或者 INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
• 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、COMMIT 或 ROLLBACK。
  MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。