看了这两本书中关于触发器的介绍,觉得太基础,没有什么内容,只是为了纯粹说明知识点,也没有应用场景举例,可能我对国内的书籍要求太高。等于这里还是有个坑,等着以后有机填吧
触发器
触发器可以理解为一种回调。主要关键词有insert, update,delete,before, after等
创建触发器
create trigger aft_insert_data after insert
on test for each row set @msg='insert one row data';
执行过程
MariaDB [oldboy]> create trigger aft_insert_data after insert
-> on test for each row set @msg='insert one row data';
Query OK, 0 rows affected (0.01 sec)
MariaDB [oldboy]> select @msg;
+------+
| @msg |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
# 插入数据
MariaDB [oldboy]> insert into test values(7,30,'saul', 'male');
Query OK, 1 row affected (0.01 sec)
MariaDB [oldboy]> select @msg;
+---------------------+
| @msg |
+---------------------+
| insert one row data |
+---------------------+
1 row in set (0.00 sec)
查看触发器
MariaDB [oldboy]> show triggers \G
*************************** 1. row ***************************
Trigger: aft_insert_data
Event: INSERT
Table: test
Statement: set @msg='insert one row data'
Timing: AFTER
Created: 2020-03-27 17:50:50.86
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
#第二种方式
MariaDB [oldboy]> select * from information_schema.triggers where trigger_name='aft_insert_data' \G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: oldboy
TRIGGER_NAME: aft_insert_data
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: oldboy
EVENT_OBJECT_TABLE: test
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: set @msg='insert one row data'
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-03-27 17:50:50.86
SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@%
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)
删除触发器
database_name.trigger_name
drop trigger oldboy.aft_insert_data;
ref: mysql网络数据库设计 mysql5.7从入门到精通