MySQL的触发器

触发器(Trigger)是由事件来触发某个操作。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。

创建触发器

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

\\create trigger 触发器名 before|after 触发事件 on 表名
\\      for each row 执行语句
mysql> create trigger user_trig1 before insert on user for each row insert into  trigger_time values(now());

mysql> insert into user(id,name,num,phone,address,age,sex) values(2,'su bowen',2,1861225,'tongzhou',4,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+------+---------+----------+-----+------+
| id | name        | num  | phone   | address  | age | sex  |
+----+----------+------+---------+----------+-----+------+
|  1 | su jingxuan |    1 | 1861109 | beijing  |  35 |    1 |
|  2 | su bowen    |    2 | 1861225 | tongzhou |   4 |    1 |
mysql> select * from trigger_time;
+---------------------+
| exec_time           |
+---------------------+
| 2017-08-13 16:22:14 |
+---------------------+

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

一般情况下,MySQL默认使用;作为结束执行语句。在创建触发器过程中需要用到;。为了解决这个问题可以用DELIMITER语句。如"delimiter&&",可以将结束符号变成&&。当触发器创建完成之后,可以用命令delimiter;来将结束符号还原。

mysql> delimiter&&
mysql> create trigger user_trig2 after delete on user for each row begin insert into trigger_time values('21:00:01'); insert into trigger_time values('22:00:02'); end&&
mysql>delimiter ;
mysql> create table trigger_time(exec_time time);
mysql> delete from user where id=2;
mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 21:00:01  |
| 22:00:02  |
+-----------+
mysql> select * from user;                                                            
+----+-------------+------+---------+---------+-----+------+
| id | name        | num  | phone   | address | age | sex  |
+----+-------------+------+---------+---------+-----+------+
|  1 | su jingxuan |    1 | 1861109 | beijing |  35 |    1 |
+----+-------------+------+---------+---------+-----+------+

查看触发器

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: user_trig1
               Event: INSERT
               Table: user
           Statement: insert into  trigger_time values(now())
              Timing: BEFORE
             Created: 2017-08-13 16:20:33.95
            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: sujx@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: user_trig2
               Event: DELETE
               Table: user
           Statement: begin
insert into trigger_time values('21:00:01');
insert into trigger_time values('22:00:02');
end
              Timing: AFTER
             Created: 2017-08-13 16:30:24.44
            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: sujx@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
\\可以在trigger表中查看触发器信息
mysql> select * from information_schema.triggers\G

触发器的使用

在MySQL中,触发器执行的顺序是before触发器、表操作、after触发器。

删除触发器

mysql> drop trigger user_trig1;

Leave a Reply

Your email address will not be published. Required fields are marked *