Mysql数据库（七）触发器

　　一、MySQL触发器

　　触发器是由MySQL的基本命令事件来触发某种特定操作，这些基本的命令由INSERT、UPDATE、DELETE等事件来触发某些特定操作。满足触发器的触发条件时，数据库系统就会自动执行触发器中定义的程序语句。这样可以令某些操作之间的一致性得到协调。

　　1.创建MySQL触发器　　

mysql> CREATE TABLE IF NOT EXISTS tb_booklog(
    -> id int(11) PRIMARY KEY auto_increment NOT NULL,
    -> event varchar(200) NOT NULL,
    -> logtime timestamp NOT NULL DEFAULT current_timestamp
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER auto_save_log BEFORE INSERT
    -> ON tb_bookinfo FOR EACH ROW
    -> INSERT INTO tb_booklog(event,logtime) values('look at me',now());
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM tb_bookinfo;
    -> //
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
| 17120108 | Lian      |      1 | QiaoJiang | 116  | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |
| 17120109 | Tian King |      2 | TianJiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
3 rows in set (0.00 sec)

mysql> INSERT INTO tb_bookinfo
    -> (barcode,bookname,typeid,author,ISBN,price,page,bookcase,inTime,del,id)
    -> VALUES
    -> ('17120110','ShenOba',4,'ShenJiang','118',52.15,300,4,'2018-04-20',0,4);
    -> //
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb_bookinfo;
    -> //
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
| 17120108 | Lian      |      1 | QiaoJiang | 116  | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |
| 17120109 | Tian King |      2 | TianJiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |
| 17120110 | ShenOba   |      4 | ShenJiang | 118  | 52.15 |  300 |        4 | 2018-04-20 |    0 |  4 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tb_booklog;
    -> //
+----+------------+---------------------+
| id | event      | logtime             |
+----+------------+---------------------+
|  2 | look at me | 2018-04-18 19:40:02 |
+----+------------+---------------------+
1 row in set (0.00 sec)

 　　2.创建具有多个执行语句的触发器

mysql> CREATE DEFINER=`root`@`localhost` TRIGGER delete_book_info BEFORE DELETE
    -> ON tb_bookinfo FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tb_booklog(event,logtime) values('let me go',now());
    -> INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

 　　血一般惨痛的教训！！！当删除表中某一条记录未能成功时，检查一下触发器什么的有没有INSERT INTO语句，如果有，检查插入值的个数和表能不能对应起来，这里的原因就是两个表不一致！！！

Microsoft Windows [版本 10.0.16299.371]
(c) 2017 Microsoft Corporation。保留所有权利。

C:\Users\BigJun>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER BEFORE DELETE
    -> ON tb_bookinfo FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tb_booklog(event,logtime) values('let me go'.now());
    -> INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
    -> END
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEFORE DELETE
ON tb_bookinfo FOR EACH ROW
BEGIN
INSERT INTO tb_booklog(event,log' at line 1
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER delete_book_info BEFORE DELETE
    -> ON tb_bookinfo FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tb_booklog(event,logtime) values('let me go'.now());
    -> INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
    -> END
    -> //
ERROR 1046 (3D000): No database selected
mysql> use db_library;
ERROR 1049 (42000): Unknown database 'db_library;'
mysql> USE db_library;
ERROR 1049 (42000): Unknown database 'db_library;'
mysql> exit
Bye

C:\Users\BigJun>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_database13      |
| db_library         |
| db_library1        |
| db_library_gbk     |
| hotel              |
| mybatis            |
| mysql              |
| mysqldb            |
| performance_schema |
| se                 |
| spring             |
| springapp          |
| sys                |
| test               |
+--------------------+
15 rows in set (0.00 sec)

mysql> use db_library;
Database changed
mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER delete_book_info BEFORE DELETE
    -> ON tb_bookinfo FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tb_booklog(event,logtime) values('let me go'.now());
    ->
    -> INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
    -> END
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.now());

INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
' at line 4
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER delete_book_info BEFORE DELETE
    -> ON tb_bookinfo FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tb_booklog(event,logtime) values('let me go'.now());
    -> INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
    -> END
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.now());
INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
E' at line 4
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER delete_book_info BEFORE DELETE
    -> ON tb_bookinfo FOR EACH ROW
    -> BEGIN
    -> INSERT INTO tb_booklog(event,logtime) values('let me go',now());
    -> INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tb_bookinfo;
    -> //
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
| 17120108 | Lian      |      1 | QiaoJiang | 116  | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |
| 17120109 | Tian King |      2 | TianJiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |
| 17120110 | ShenOba   |      4 | ShenJiang | 118  | 52.15 |  300 |        4 | 2018-04-20 |    0 |  4 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
4 rows in set (0.01 sec)

mysql> select * from tb_booklog;
    -> //
+----+------------+---------------------+
| id | event      | logtime             |
+----+------------+---------------------+
|  2 | look at me | 2018-04-18 19:40:02 |
+----+------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb_bookinfobak;
    -> //
Empty set (0.00 sec)

mysql> DELETE FROM tb_bookinfo where id=4;
    -> //
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> DELETE FROM tb_bookinfo WHERE id=4;
    -> //
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> DELETE FROM tb_bookinfo WHERE id=3;
    -> //
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> DELETE FROM tb_bookinfo WHERE barcode='17120107';
    -> //
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> show triggers;
    -> //
+------------------+--------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger          | Event  | Table       | Statement                                                                                                                                        | Timing | Created                | sql_mode                                                                                                                                  | Definer        | character_set_client | collation_connection | Database Collation |
+------------------+--------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| delete_book_info | DELETE | tb_bookinfo | BEGIN
INSERT INTO tb_booklog(event,logtime) values('let me go',now());
INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
END | BEFORE | 2018-04-18 20:35:07.19 | 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 | root@localhost | gbk                  | gbk_chinese_ci       | utf8_general_ci    |
+------------------+--------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from tb_bookinfo;
    -> //
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
| 17120108 | Lian      |      1 | QiaoJiang | 116  | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |
| 17120109 | Tian King |      2 | TianJiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |
| 17120110 | ShenOba   |      4 | ShenJiang | 118  | 52.15 |  300 |        4 | 2018-04-20 |    0 |  4 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
4 rows in set (0.00 sec)

mysql> delete from tb_bookinfo where id=1;
    -> //
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> desc tb_bookinfobak;
    -> //
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| barcode    | varchar(30)      | YES  |     | NULL    |       |
| bookname   | varchar(70)      | YES  |     | NULL    |       |
| typeid     | int(10) unsigned | YES  |     | NULL    |       |
| author     | varchar(30)      | YES  |     | NULL    |       |
| ISBN       | varchar(20)      | YES  |     | NULL    |       |
| price      | float(8,2)       | YES  |     | NULL    |       |
| page       | int(10) unsigned | YES  |     | NULL    |       |
| bookcase   | int(10) unsigned | YES  |     | NULL    |       |
| inTime     | datetime(6)      | YES  |     | NULL    |       |
| del        | tinyint(1)       | YES  |     | 0       |       |
| id         | int(11)          | NO   |     | NULL    |       |
| translator | varchar(30)      | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

mysql> alter table tb_bookinfobak drop transtor;
    -> //
ERROR 1091 (42000): Can't DROP 'transtor'; check that column/key exists
mysql> alter table tb_bookinfobak drop translator;
    -> //
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_bookinfobak;
    -> //
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | datetime(6)      | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> desc tb_bookinfo;
    -> //
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | date             | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   | PRI | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> alter table tb_bookinfobak modify inTime date;
    -> //
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_bookinfobak;
    -> //
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode  | varchar(30)      | YES  |     | NULL    |       |
| bookname | varchar(70)      | YES  |     | NULL    |       |
| typeid   | int(10) unsigned | YES  |     | NULL    |       |
| author   | varchar(30)      | YES  |     | NULL    |       |
| ISBN     | varchar(20)      | YES  |     | NULL    |       |
| price    | float(8,2)       | YES  |     | NULL    |       |
| page     | int(10) unsigned | YES  |     | NULL    |       |
| bookcase | int(10) unsigned | YES  |     | NULL    |       |
| inTime   | date             | YES  |     | NULL    |       |
| del      | tinyint(1)       | YES  |     | 0       |       |
| id       | int(11)          | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> select * from tb_bookinfo;
    -> //
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
| 17120108 | Lian      |      1 | QiaoJiang | 116  | 50.00 |  351 |        2 | 2018-04-18 |    0 |  2 |
| 17120109 | Tian King |      2 | TianJiang | 117  | 51.10 |  352 |        3 | 2018-04-19 |    0 |  3 |
| 17120110 | ShenOba   |      4 | ShenJiang | 118  | 52.15 |  300 |        4 | 2018-04-20 |    0 |  4 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
4 rows in set (0.00 sec)

mysql> select * from tb_bookbak;
    -> //
ERROR 1146 (42S02): Table 'db_library.tb_bookbak' doesn't exist
mysql> select * from tb_bookinfobak;
    -> //
Empty set (0.00 sec)

mysql> select * from tb_booklog;
    -> //
+----+------------+---------------------+
| id | event      | logtime             |
+----+------------+---------------------+
|  2 | look at me | 2018-04-18 19:40:02 |
+----+------------+---------------------+
1 row in set (0.00 sec)

mysql> delete from tb_bookinfo where id=1;
    -> //
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_bookinfobak;
    -> //
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| barcode  | bookname  | typeid | author    | ISBN | price | page | bookcase | inTime     | del  | id |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
| 17120107 | Java King |      3 | LianJiang | 115  | 49.80 |  350 |        1 | 2018-04-17 |    0 |  1 |
+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+
1 row in set (0.00 sec)

mysql> select * from tb_booklog;
    -> //
+----+------------+---------------------+
| id | event      | logtime             |
+----+------------+---------------------+
|  2 | look at me | 2018-04-18 19:40:02 |
|  8 | let me go  | 2018-04-18 20:54:19 |
+----+------------+---------------------+
2 rows in set (0.00 sec)

 　　二、查看触发器

　　1.SHOW TRIGGER语句

mysql> SHOW TRIGGERs\G
*************************** 1. row ***************************
             Trigger: delete_book_info
               Event: DELETE
               Table: tb_bookinfo
           Statement: BEGIN
INSERT INTO tb_booklog(event,logtime) values('let me go',now());
INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
END
              Timing: BEFORE
             Created: 2018-04-18 20:35:07.19
            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: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

　　2.查看triggers表中的触发信息

　　（1）查看所有触发器的定义

mysql> SELECT * FROM information_schema.triggers\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: db_library
              TRIGGER_NAME: delete_book_info
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: db_library
        EVENT_OBJECT_TABLE: tb_bookinfo
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
INSERT INTO tb_booklog(event,logtime) values('let me go',now());
INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
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: 2018-04-18 20:35:07.19
                  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: gbk
      COLLATION_CONNECTION: gbk_chinese_ci
        DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_insert_set_user
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; 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: 2018-01-08 16:14:42.70
                  SQL_MODE:
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
*************************** 3. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_update_set_user
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; 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: 2018-01-08 16:14:42.70
                  SQL_MODE:
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
3 rows in set (0.03 sec)

 　　（2）查看指定触发器名称的触发器的定义（指定数据库时将NAME改成SCHEMA，等号后面改成数据库名）

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='delete_book_info'\G;
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: db_library
              TRIGGER_NAME: delete_book_info
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: db_library
        EVENT_OBJECT_TABLE: tb_bookinfo
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
INSERT INTO tb_booklog(event,logtime) values('let me go',now());
INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo where id=OLD.id;
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: 2018-04-18 20:35:07.19
                  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: gbk
      COLLATION_CONNECTION: gbk_chinese_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.01 sec)

　　三、使用触发器

　　1.触发器的执行顺序

mysql> create table if not exists tb_temp(
    -> id int(11) PRIMARY KEY auto_increment NOT NULL,
    -> event varchar(200) NOT NULL,
    -> time timestamp NOT NULL DEFAULT current_timestamp
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table tb_bookcase(
    -> id int(11) PRIMARY KEY auto_increment NOT NULL,
    -> name varchar(200)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create trigger before_in BEFORE INSERT ON
    -> tb_bookcase FOR EACH ROW
    -> INSERT INTO tb_temp(event) values('BEFORE INSERT');
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger after_in AFTER INSERT ON
    -> tb_bookcase FOR EACH ROW
    -> INSERT INTO tb_temp(event) values('AFTER INSERT');
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tb_bookcase;
Empty set (0.00 sec)

mysql> select * from tb_temp;
Empty set (0.00 sec)

mysql> INSERT INTO tb_bookcase(name) VALUES('左边第二个');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_bookcase;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 左边第二个      |
+----+-----------------+
1 row in set (0.00 sec)

mysql> select * from tb_temp;
+----+---------------+---------------------+
| id | event         | time                |
+----+---------------+---------------------+
|  1 | BEFORE INSERT | 2018-04-19 08:17:28 |
|  2 | AFTER INSERT  | 2018-04-19 08:17:28 |
+----+---------------+---------------------+
2 rows in set (0.00 sec)

　　2.使用触发器维护冗余数据（为了避免数据不一致问题的发生，尽量不要人工维护数据，建议通过编程自动维护）

mysql> create table tb_stock(
    -> id int(11) PRIMARY KEY auto_increment NOT NULL.
    -> id int(11) PRIMARY KEY auto_increment NOT NULL.^C
mysql> create table tb_stock(
    -> id int(11) PRIMARY KEY auto_increment NOT NULL,
    -> goodsname varchar(200) NOT NULL,
    -> number int(11)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tb_stock;
+----+-------------+--------+
| id | goodsname   | number |
+----+-------------+--------+
|  1 | 小猪佩qi    |    100 |
+----+-------------+--------+
1 row in set (0.00 sec)

mysql> create table tb_sell(
    -> id int(11) PRIMARY KEY auto_increment NOT NULL,
    -> goodsname varchar(200),
    -> number int(11)
    -> );
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tb_sell;
    -> //
Empty set (0.00 sec)

mysql> CREATE TRIGGER auto_number AFTER INSERT
    -> ON tb_sell FOR EACH ROW
    -> BEGIN
    -> DECLARE sellnum int(10);
    -> SELECT number FROM tb_sell where id=NEW.id INTO @sellnum;
    -> UPDATE tb_stock SET number=number-@sellnum WHERE goodsname='小猪佩qi';
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tb_sell(goodsname,number) VALUES
    -> ('小猪佩qi',2);
    -> //
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_sell;
    -> //
+----+-------------+--------+
| id | goodsname   | number |
+----+-------------+--------+
|  1 | 小猪佩qi    |      2 |
+----+-------------+--------+
1 row in set (0.00 sec)

mysql> select * from tb_stock;
    -> //
+----+-------------+--------+
| id | goodsname   | number |
+----+-------------+--------+
|  1 | 小猪佩qi    |     98 |
+----+-------------+--------+
1 row in set (0.00 sec)

　　四、删除触发器