一起学mysql 04.mysql 触发器

mysql 触发器

创建触发器

创建只有一个语句的触发器

mysql> create table trigger_time (exec_time time);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from department;
+-------+-----------+-----------------+-------------+
| d_id  | d_name    | function        | address     |
+-------+-----------+-----------------+-------------+
| 10001 | 科研部    | 新产品科研      | 3号楼5层    |
| 10002 | 生成部    | 主管生产        | 5号楼1层    |
+-------+-----------+-----------------+-------------+
2 rows in set (0.00 sec)

mysql> create trigger dept_trig1 before insert on department for each row  
    -> insert into trigger_time values(now());
Query OK, 0 rows affected (0.00 sec)

mysql> insert into department values(10003,'销售部','负责产品销售','1号楼销售大厅');
Query OK, 1 row affected (0.00 sec)

mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 19:52:11  |
+-----------+
1 row in set (0.00 sec)

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

mysql> delimiter &&
mysql> create trigger dept_trig2 after  delete on department for each row
    -> BEGIN
    ->     insert into trigger_time values(now());
    ->     insert into trigger_time values('22:01:01');
    -> END
    -> &&
mysql> delimiter ;

mysql> delete from department where d_id='10003';
Query OK, 1 row affected (0.01 sec)

mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 19:52:11  |
| 20:05:00  |
| 22:01:01  |
+-----------+
3 rows in set (0.00 sec)

查看触发器

show triggers 语句查看触发器信息

mysql> show triggers \G;
*************************** 1. row ***************************
             Trigger: dept_trig1
               Event: INSERT
               Table: department
           Statement: insert into trigger_time values(now())
              Timing: BEFORE
             Created: 2019-07-25 19:49:11.55
            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: skip-grants user@skip-grants host
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: dept_trig2
               Event: DELETE
               Table: department
           Statement: BEGIN
    insert into trigger_time values(now());
    insert into trigger_time values('22:01:01');
END
              Timing: AFTER
             Created: 2019-07-25 20:02:41.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: skip-grants user@skip-grants host
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

在triggers表中查看触发器信息

mysql> select * from information_schema.triggers where trigger_name='dept_trig2' \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: example
              TRIGGER_NAME: dept_trig2
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: example
        EVENT_OBJECT_TABLE: department
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
    insert into trigger_time values(now());
    insert into trigger_time values('22:01:01');
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: 2019-07-25 20:02:41.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: skip-grants user@skip-grants host
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.12 sec)

触发器的使用

mysql> create table trigger_test (id int(4) primary key not null auto_increment, info varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger before_insert  before  insert  on department for each row insert into trigger_test values(null,'before insertt');
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger after_insert  after  insert  on department for each row insert into trigger_test values(null,'after insert');;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into department values(10003,'销售部','负责产品销售','1号楼销售大厅');
Query OK, 1 row affected (0.00 sec)

mysql> select * from trigger_test;
+----+---------------+
| id | info          |
+----+---------------+
|  1 | before insert |
|  2 | after insert  |
+----+---------------+
2 rows in set (0.00 sec)

删除触发器

mysql> drop trigger before_insert;