MySQL使用教程【八】：触发器的操作

触发器是由事件来触发某个操作，这些事件包括insert语句、update语句和delete语句。当数据库系统执行这些事件时，会激活触发器执行相应操作。MySQL从5.0.2开始支持触发器。使用触发器可以保证某些操作之间的一致性。

1、创建触发器

（1）创建有一条执行语句的触发器

【语法】

create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句;

• before和after参数指定触发器执行的时间;
• 触发事件包括insert、update和delete;
• for each row表示任何一条记录上的操作满足触发条件都会触发触发器;
• 执行语句指触发器被触发后执行的程序

【实例】创建触发器tri_diarytime，当向部门表t_dept中插入任意一条数据时，就会在插入操作之前向表t_diary中插入当前的事件记录。

mysql> use test;
Database changed
mysql> create table t_dept(
    -> deptno int,
    -> dname varchar(20),
    -> loc varchar(40)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)


mysql> create table t_diary(
    -> diaryno int primary key auto_increment,
    -> tablename varchar(20),
    -> diarytime datetime
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t_diary;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| diaryno   | int(11)     | NO   | PRI | NULL    | auto_increment |
| tablename | varchar(20) | YES  |     | NULL    |                |
| diarytime | datetime    | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec) 
创建触发器tri_diarytime
mysql> create trigger tri_diarytime
    ->  before insert
    ->   on t_dept for each row 
    ->    insert into t_diary values(null,'t_dept',now());
Query OK, 0 rows affected (0.04 sec)

插入记录校验触发器
mysql> insert into t_dept values(1,'test','shangxi');
Query OK, 1 row affected (0.52 sec)

mysql> select * from t_diary;
+---------+-----------+---------------------+
| diaryno | tablename | diarytime           |
+---------+-----------+---------------------+
|       1 | t_dept    | 2019-03-07 15:07:24 |
+---------+-----------+---------------------+
1 row in set (0.00 sec)

（2）创建包含多条执行语句的触发器

【语法】

create trigger 触发器名 before|after 触发事件 on 表名 for each row begin 执行语句列表 end;

【实例】创建触发器tri_diarytime2，当向部门表t_dept中插入任意一条数据时，就会在插入操作之后向表t_diary中插入两条记录。

mysql> delimiter $$
mysql> create trigger tir_diarytime2
    ->  after insert on t_dept for each row
    ->   begin
    ->    insert into t_diary values(null,'t_dept',now());
    ->    insert into t_diary values(null,'t_dept',now());
    ->   end
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_dept values(2,'test','shangxi');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_diary;
+---------+-----------+---------------------+
| diaryno | tablename | diarytime           |
+---------+-----------+---------------------+
|       1 | t_dept    | 2019-03-07 15:07:24 |
|       2 | t_dept    | 2019-03-07 15:38:37 |
|       3 | t_dept    | 2019-03-07 15:38:37 |
+---------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> delimiter ;;

2、查看触发器

（1）通过show triggers语句查询

mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: tri_diarytime
               Event: INSERT
               Table: t_dept
           Statement: insert into t_diary values(null,'t_dept',now())
              Timing: BEFORE
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: tir_diarytime2
               Event: INSERT
               Table: t_dept
           Statement: begin
   insert into t_diary values(null,'t_dept',now());
   insert into t_diary values(null,'t_dept',now());
  end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

（2）通过查看系统表triggers查看触发器

#选择数据库
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

#查看所有触发器
mysql> select * from triggers \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: tri_diarytime
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: t_dept
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: insert into t_diary values(null,'t_dept',now())
        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
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: tir_diarytime2
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: t_dept
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
   insert into t_diary values(null,'t_dept',now());
   insert into t_diary values(null,'t_dept',now());
  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: NULL
                  SQL_MODE: 
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
2 rows in set (0.00 sec)

#查看指定的触发器
mysql> select * from triggers where trigger_name='tri_diarytime' \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: tri_diarytime
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: t_dept
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: insert into t_diary values(null,'t_dept',now())
        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
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

3、删除触发器
语法：drop trigger 触发器名;

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop trigger tri_diarytime;
Query OK, 0 rows affected (0.03 sec)

mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: tir_diarytime2
               Event: INSERT
               Table: t_dept
           Statement: begin
   insert into t_diary values(null,'t_dept',now());
   insert into t_diary values(null,'t_dept',now());
  end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)