一、含义
触发器是由事件触发而自动执行某个操作,这个事件包括INSERT语句、UPDATE语句和DELETE语句。触发器是特殊的储存过程,而不同于储存过程,储存过程可以通过存储名字调用。
二、特点
触发事件的操作和触发器里的SQL语句是一个事务操作,具有原子性,要么全部执行,要么都不执行。
三、案例运行环境
MySQL:8.0.2
# 案例两张表
MySQL [test]> SELECT * FROM tb_user;
+----+----------+----------+---------------------+
| id | realname | integral | cretaed_at |
+----+----------+----------+---------------------+
| 1 | 王维 | 10 | 2020-01-06 15:17:41 |
| 2 | 李白 | 0 | 2020-01-06 15:14:06 |
+----+----------+----------+---------------------+
2 rows in set (0.00 sec)
MySQL [test]> SELECT * FROM tb_article;
+----+-----+--------+---------------------------------------------------------------------------+---------------------+
| id | uid | title | content | created_at |
+----+-----+--------+---------------------------------------------------------------------------+---------------------+
| 1 | 1 | 相思 | 红豆生南国,春来发几枝。 愿君多采撷,此物最相思。 | 2020-01-06 13:24:26 |
+----+-----+--------+---------------------------------------------------------------------------+---------------------+
1 row in set (0.01 sec)
四、创建触发器
- 创建触发器语法
CREATE [DEFINER = user] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
-
参数解释
trigger_name:触发器名称。 trigger_time:触发器执行时间;BEFORE表示在触发器事件之前执行触发器语句,AFTER表示在触发器事件之后执行触发器语句。 trigger_event:触发器事件,即触发器执行条件,包含INSERT、UPDATE、DELETE语句。 tbl_name:触发事件的操作表名。 FOR EACH ROW:任何一条记录上的操作满足出发事件都会触发该触发器。 trigger_order:多个触发器作用同一张表,可以定义执行顺序。 trigger_body:激活触发器后被执行的语句。
-
创建触发器
DELIMITER // CREATE TRIGGER tri_integral AFTER INSERT ON tb_article FOR EACH ROW BEGIN DECLARE uid INT; --获取用户ID SET uid= NEW.uid; UPDATE tb_user SET integral = integral + 10 WHERE id = uid; END; // DELIMITER ; # 发布一篇文章,用户积分自动增加10; MySQL [test]> INSERT INTO `tb_article` (`uid`, `title`, `content`) VALUES ('1', '画', '远看山有色,近听水无声。春去花还在,人来鸟不惊。'); Query OK, 1 row affected (0.01 sec) MySQL [test]> SELECT * FROM tb_user; +----+----------+----------+---------------------+ | id | realname | integral | cretaed_at | +----+----------+----------+---------------------+ | 1 | 王维 | 20 | 2020-01-06 15:17:41 | | 2 | 李白 | 0 | 2020-01-06 15:14:06 | +----+----------+----------+---------------------+ 2 rows in set (0.00 sec)
五、查看触发器
- 通过SHOW TRIGGERS语句查看触发器
MySQL [test]> SHOW TRIGGERS \G; *************************** 1. row *************************** Trigger: tri_integral Event: INSERT Table: tb_article Statement: BEGIN DECLARE uid INT; SET uid= NEW.uid; UPDATE tb_user SET integral = integral + 10 WHERE id = uid; END Timing: AFTER Created: 2020-01-06 15:16:05.49 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@% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
-
通过information_schema.TRIGGERS查看触发器
MySQL [test]> SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'tri_integral' \G; *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: tri_integral EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: tb_article ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: BEGIN DECLARE uid INT; SET uid= NEW.uid; UPDATE tb_user SET integral = integral + 10 WHERE id = uid; 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-01-06 15:16:05.49 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@% CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.01 sec)
六、删除触发器
- 使用DROP TRIGGER删除
MySQL [test]> DROP TRIGGER tri_integral; Query OK, 0 rows affected (0.01 sec)
七、触发器优缺点
- 优点
- 触发器可以加强数据库表中数据的完整性约束和业务规则;
- 同步实时复制表中数据;
- 缺点
- 增加了程序的复杂性;
八、其他
MySQL触发器关键字:NEW 和 OLD;
使用方法: NEW.columnName (columnName 为相应数据表某一列名)
INSERT型触发器:NEW代表要插入的新数据;
UPDATE型触发器:OLD代表要修改的原数据,NEW表示要修改为的新数据;
DELETE型触发器:OLD表示要删除的原数据;
OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值。